Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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