Help on Pivot Table Aggregation

[#1]

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.

[avinashelite]

Can you please post your qvw.

[#3]

No sorry, I can't...

[avinashelite]

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

[#5]

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.

[avinashelite]

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

[#7]

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