Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Partner
Partner

Semi Additive Aggregation

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

1 Solution

Accepted Solutions

Re: Semi Additive Aggregation

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

2 Replies

Re: Semi Additive Aggregation

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

Partner
Partner

Re: Semi Additive Aggregation

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