topic Re: Help on Pivot Table Aggregation in QlikView

Help on Pivot Table Aggregation

Thu, 10 Jul 2014 09:31:58 GMT

Hi,

I have the following Pivot table and I'm trying to get the Cells with question mark populated correctly.

Dimensions: - Division

- Manager level

- Zone

Expr: - Headcount: Count({$<[Headcount.JLS]={'1'}>}(%Employee_ID))

- %: Count({$<[Headcount.JLS]={'1'}>}(%Employee_ID))

AGGR(COUNT({<[Headcount.JLS]={'1'}>}(%Employee_ID))

,[Headcount.Division]

,[Headcount.Manager Level]

)

I would expect to see the value "37.5%" in the % CRO/Edinburgh/Appointed cell and also a value in the "Other UK" cell.

What am i doing wrong here?

Thanks for your help.

Re: Help on Pivot Table Aggregation

avinashelite — Thu, 10 Jul 2014 09:35:26 GMT

Can you please post your qvw.

Re: Help on Pivot Table Aggregation

Thu, 10 Jul 2014 09:37:54 GMT

No sorry, I can't...

Re: Help on Pivot Table Aggregation

avinashelite — Thu, 10 Jul 2014 09:44:13 GMT

Can you please provide the sample data?? not the actually qvw file because its hard to find the error without seeing the data

Re: Help on Pivot Table Aggregation

Thu, 10 Jul 2014 09:53:00 GMT

There's no issue with the data...

I know I can create a table to get everything working by creating 2 Expressions (Headcount and %) for each zone.

Then I'd just need to change my expression (for %) to something like:

Count({$<[Headcount.JLS]={'1'},[Headcount.Work Cost Zone]={'London'}>}(%Employee_ID))

COUNT({<[Headcount.JLS]={'1'}>}(%Employee_ID))

And then repeat the process for each Zone to get the full table.

I can already work it out but I'm sure there's probably a way to make it work.

I'm just wondering why Pivoting the Zone dimension in my Pivot table doesn't give me the correct result.

Re: Help on Pivot Table Aggregation

avinashelite — Thu, 10 Jul 2014 10:04:55 GMT

As per my understanding your getting the current headcount and trying to divide it with the total headcount?? if so please try like this :

Count({$<[Headcount.JLS]={'1'}>}(%Employee_ID))

AGGR(COUNT({1<[Headcount.JLS]={'1'}>}(%Employee_ID))

,[Headcount.Division]

,[Headcount.Manager Level]

)

or Count({$<[Headcount.JLS]={'1'}>}(%Employee_ID)) /Count({1<[Headcount.JLS]={'1'}>}(%Employee_ID))

Re: Help on Pivot Table Aggregation

Thu, 10 Jul 2014 10:26:22 GMT

Nope,

The first one gives the same result, the 2nd one gives 100% everywhere....

That's okay don't worry, I'm just going to use my backup solution for now...