2 Replies Latest reply: Oct 17, 2013 10:38 AM by Robert Hoekstra RSS

    Semi Additive Aggregation

    Robert Hoekstra

      Dear Qlikies,

       

      I would like to create ONE expression to calculate an Headcount.

       

      It would be nice if the expression could:

      1. show the Sum(Headcount) over any dimension other than Time.
      2. show the Last value of Headcount over time dimension.
      3. repeat the Last non null value for each month.

       

      See example attached.

       

      Any help would be greatly appreciated!

       

      Thanks,

      Robert

        • Re: Semi Additive Aggregation
          Henric Cronström

          There could be a simpler way to do this, but my (quick) solution is the following:

           

          1) Don't use a calculated dimension. Define Month in the script.

          2) Pad missing values (in the script) by using Peek():

           

               // ============================

               RawData: Load * From Source;

           

               CartesianProduct: Load distinct Month Resident RawData;

               Join Load distinct Department Resident RawData;

           

               Join (RawData) Load * Resident CartesianProduct;

           

               Data: Load *,

                 If(IsNull(Headcount) and Department=Peek(Department),Peek(CorrectedHeadcount),

                    Headcount) as CorrectedHeadcount

                 Resident RawData Order By Department, Month;

           

               Drop Table RawData, CartesianProduct;

               // ============================

           

          3) Use the following calculation in the chart:

           

               If( Count(distinct Month)=1,

                 Sum(CorrectedHeadcount),

                 If( Count(distinct Department)=1,

                 FirstSortedValue(Aggr(sum(CorrectedHeadcount),Month,Department),-Month),

                 Sum(Aggr(FirstSortedValue(Aggr(sum(CorrectedHeadcount),Month,Department),-Month),Department))

                 ))

           

          HIC

            • Re: Semi Additive Aggregation
              Robert Hoekstra

              Hi Henric,

               

              Thanks a lot for your solution that does provide the correct results.

               

              I do have two comments:

              1/ In such a case, I would love to be able to create the chart without having to change the data model.

              2/ I'm affraid this formula might not work very well with big data volumes.

               

              It would be great if we could have semi-additive functions built in. I know it does exist in SQL Server for example.

              Do you think there would be any chance to have this in future versions?

               

              Thanks,

              Robert