Semi Additive Aggregation

[1000qlik]

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

[hic · Accepted solution]

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

[1000qlik]

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