Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Help on Pivot Table Aggregation

Hi,

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

pivot.png

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.

Tags (3)
6 Replies
Highlighted

Re: Help on Pivot Table Aggregation

Can you please post your qvw.

Highlighted
Not applicable

Re: Help on Pivot Table Aggregation

No sorry, I can't...

Highlighted

Re: Help on Pivot Table Aggregation

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

Highlighted
Not applicable

Re: Help on Pivot Table Aggregation

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.

Highlighted

Re: Help on Pivot Table Aggregation

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

Highlighted
Not applicable

Re: Help on Pivot Table Aggregation

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...