topic Re: Semi Additive Aggregation in QlikView

Semi Additive Aggregation

1000qlik — Mon, 10 Jun 2013 17:12:15 GMT

Dear Qlikies,

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

It would be nice if the expression could:

See example attached.

Any help would be greatly appreciated!

Thanks,

Robert

hic — Tue, 15 Oct 2013 14:01:51 GMT

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 — Thu, 17 Oct 2013 14:38:41 GMT

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