3 Replies Latest reply: Jan 8, 2016 11:47 AM by Ben Sughrue RSS

    Set Analysis for a semi-additive metric

    Ben Sughrue

      I am currently attempting to replace an old Essbase cube with a Qlik Sense app. The cube pulls it's data from a semi-additive daily snapshot table in our data warehouse. The metric fields in that table are semi-additive in the sense that they can be summed across all dimensions except time.

       

      To match the functionality of the cube, I have created several drill down dimensions, including one called Period (Year>Quarter>Month>Date). I would like to create a set analysis that sums the metrics by all dimensions, except the Period dimension. In the case of the Period dimension, I would like to app to display the last value (most recent) in the selected period.

       

      For example:

       

      When I am on the year level of the Period dimension, the app should show the metric value for the date of:

       

      2014: 12/31/2014

      2015: 12/22/2015 (that is the most recent snapshot as of today)

       

      When I am on the quarter level of the Period dimension, the app should show the metric value for the date of

       

      Q3-15: 9/30/2015

      Q4-15: 12/22/2015 (that is the most recent snapshot as of today)

       

      When I am on the month level of the Period dimension, the app should show the metric value for the date of:

       

      Sep-15: 9/30/2015

      Oct-15: 10/31/2015

      Nov-15: 11/30/2015

      Dec-15: 12/22/2015 (that is the most recent snapshot as of today)

       

      I have been able to connect the app directly to the cube, but I would like to eventually get rid of the cube. So, if anyone has any suggestions on how to accomplish using set analysis, I would greatly appreciate it.

       

      Thanks,

       

      Ben

        • Re: Set Analysis for a semi-additive metric
          Sunny Talwar

          Try the FirstSortedValue() function:

           

          Sample Script:

          Table:

          LOAD Date,

            Year(Date) as Year,

            Month(Date) as Month,

            MonthName(Date) as MonthYear,

            'Q' & Ceil(Month(Date)/3) & '-' & Right(Year(Date), 2) as QuarterYear,

            Ceil(Rand() * 1000000) as Value;

          LOAD Date(MakeDate(2013, 12, 31) + RecNo()) as Date

          AutoGenerate (Today() - MakeDate(2013, 12, 31));

           

          Drill Down Group: Year -> QuarterYear -> MonthYear

          Expression: =FirstSortedValue(Value, -Date)

           

          Capture.PNG

          • Re: Set Analysis for a semi-additive metric
            Gysbert Wassenaar

            Try sum(aggr(FirstSortedValue(Amount, -Date), Dim1, Dim2,..., DimN). Replace Amount and Date with the appropriate field names and replace Dim1, Dim2,..., DimN with the field names of the chart dimensions.

            • Re: Set Analysis for a semi-additive metric
              Ben Sughrue

              Thanks for you quick responses. Both helped me get to a solution. I forgot to mention in my first post that I have more than one type of measure in my fact table, so I need to use Set Analysis in addition to the functions. Here are two of the expressions that I am using in my app:

               

              FirstSortedValue({$<[METRIC_TYPE] = {'ACCOUNT COUNT'}>} Aggr(Sum({$<[METRIC_TYPE] = {'ACCOUNT COUNT'}>} METRIC), PERIOD), -PERIOD)

              ;

              FirstSortedValue({$<[METRIC_TYPE] = {'EVENT COUNT'}>} Aggr(Sum({$<[METRIC_TYPE] = {'EVENT COUNT'}>} METRIC), PERIOD), -PERIOD)