# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor II

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

Tags (4)
1 Solution

Accepted Solutions
Employee

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():

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

CartesianProduct: Load distinct Month Resident RawData;

Join Load distinct Department Resident RawData;

Join (RawData) Load * Resident CartesianProduct;

Resident RawData Order By Department, Month;

Drop Table RawData, CartesianProduct;

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

3) Use the following calculation in the chart:

If( Count(distinct Month)=1,

If( Count(distinct Department)=1,

))

HIC

2 Replies
Employee

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():

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

CartesianProduct: Load distinct Month Resident RawData;

Join Load distinct Department Resident RawData;

Join (RawData) Load * Resident CartesianProduct;

Resident RawData Order By Department, Month;

Drop Table RawData, CartesianProduct;

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

3) Use the following calculation in the chart:

If( Count(distinct Month)=1,

If( Count(distinct Department)=1,

))

HIC

New Contributor II

Hi Henric,

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