topic Re: Dimentionality in QlikView

Dimentionality

upaliwije — Fri, 22 Sep 2017 09:00:04 GMT

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

Re: Dimentionality

vinieme12 — Fri, 22 Sep 2017 09:11:36 GMT

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

Re: Dimentionality

vinieme12 — Fri, 22 Sep 2017 09:18:11 GMT

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)

) )

Re: Dimentionality

upaliwije — Fri, 22 Sep 2017 09:23:07 GMT

Thanks Vineeth,

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

Re: Dimentionality

vinieme12 — Fri, 22 Sep 2017 09:24:59 GMT

can you post a sample?

Re: Dimentionality

upaliwije — Fri, 22 Sep 2017 09:54:50 GMT

Here I have attached my QV sample

Re: Dimentionality

vinieme12 — Fri, 22 Sep 2017 10:31:23 GMT

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

)