0

Compare two measures - each with their own measure filter

I have an insight w/ several measures from different time periods ( e.g.  current week sales, last week sales, current month, last month, current year, last year )  

For each measure column I have the appropriate filter to deliver the results specific to the time period.

Now I want to compare them ( e.g.  last week / current week to get % change )   

1) Is there a way to do this at the Insight level ( e.g. w/out going back and defining MVs for each relative time period - or columns )

2) Is there an *easy* way to do this?

3) OK - if there's not an Insight or an easy way to do it, what would you consider the best way to do this?  ;-)   

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Can the % change to be written in a formula like this?

    (sum(if(period_year=$thisyear, salesamount, 0) - sum(if(period_year=$lastyear, salesamount, 0) ) / sum(if(period_year=$lastyear, salesamount, 0)
    Like 1
    • Dylan Wan  Thanks - I'm going to give that a shot - it'll be a bit more complex ( lookups based on presentation variables ) but if I can navigate that it seems hopeful!  

      Like
  • R. A. Dawson Sr - have you looked into using Ago and ToDate functions we recently introduced? We don't support week, but it will work for Year, Quarter, Month and Day.

    https://docs.incorta.com/5.1/references-built-in-functions-analytic-todate-ago/

    Like
    • Ashwin Warrier  I think this will be very valuable, but this set of dashboards and insights is all built on a Fiscal Calendar so it won't line up nicely with the Gregorian calendar.    Thanks though - I already see applications for these functions.

      Like
  • R. A. Dawson Sr - Fiscal calendar is more challenging and we have plans in our product roadmap to make it easier.

    In the interim, I will leave you with this solution that may or may not meet your needs. To do the fiscal offset, think of a two step process, where in the first step you create the offset in an Analyzer business view, as shown below.

    In this example, I took a Fiscal year that starts Nov - Oct, for which I added an offset of 2 months to overlay the Gregorian calendar. I then applied the ago function within an insight, on top of this new business view that I created in the business schema. The Revenue for November and December 2004 now roll up into 2005 fiscal months 1 and 2 respectively.

    Small chance that this approach is helpful.

    Like
Like Follow
  • 2 wk agoLast active
  • 5Replies
  • 20Views
  • 3 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.