Dimentionality

[upaliwije]

Hi Friends

I have a issue like this my below Pivot table does not calculate the % Correctly

In Total row 100% is not shown

When I Expand the Pivot table It also does not calculate correctly

Expression I used is as follows

If(Dimensionality() = 1, count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL DISTINCT{<CODE={'1','2','5'}>}POLICY_NO),

If(Dimensionality() = 2, count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL<REASON> DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)))

Pls help

[vinieme12]

the Dimensionality() value for the last TOTAL row is 0 and in your expression you are only specifying conditions for Dimensionality 1 and 2 so also add an Else condition for last total row

[vinieme12]

i think this **<CORRECTION>**

=If(Dimensionality() = 2,

     count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL<REASON> DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)

,

If(Dimensionality() = 1,

     count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL<REASON,Vehicle_TYpe> DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)

,    

count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)

) )

[upaliwije]

Thanks Vineeth,

It works for the 1st part of my question. Pls consider 2nd part also

[vinieme12]

can you post a sample?

[upaliwije]

Here I have attached my QV sample

[vinieme12 · Accepted solution]

use a combination of Rowno() and Dimensionality() to identify if the Pivot is collapsed or expaned

=if(Dimensionality()=0 and (RowNo()=0 or isnull(RowNo()))

,count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL DISTINCT{<CODE={'1','2','5'}>}POLICY_NO),

if(Dimensionality()=1  // 1st Dimension is collapsed

,if(RowNo()>0 or isnull(RowNo())                // 1st Dimension is collapsed

,count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)

,if(RowNo()=0 ,count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL <REASON> DISTINCT{<CODE={'1','2','5'}>}POLICY_NO))  // Total for first Dimension when expanded

)

,if(Dimensionality()=2 // 1st Dimension is expanded

,if(RowNo()>0,count(DISTINCT{<CODE={'1','2','5'}>}POLICY_NO)/count(TOTAL <REASON> DISTINCT{<CODE={'1','2','5'}>}POLICY_NO))    // individual row totals

)

)

)