Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
ITMercon
Contributor II
Contributor II

Measure as a Dimension in Pivot Table

Hey everyone! I'm trying to do a pivot table using the result of one measure as the columns of the Pivot Table. 

Measure>: IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=41,'Muy Critico',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=31,'Critico',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=16,'Excede Rango',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=0,'Dentro del rango',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))<0,'Saldo Negativo',''
)))))

I made it as a measure because the buckets are the result of 2 more measures that I stored as Variables  AcumCY (Accumulated Current Year) and Acum.LY (Accumulated Current Year). I have managed to add the buckets in the pivot table using the Valuelist function and I got this, however, the numbers under each column are not correct. 

PivotTable BU vs BalanceStatus.PNG

 

I created the expression IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount)) as a variable (vFinalBalance) to incorporate it in the measure I formulated for this Table: IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Muy Critico',
Count((vFinalBalance > 41)),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Critico',
Count(distinct (if(vFinalBalance >= 31, KeyEmployee))),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Excede el Rango',
Count(distinct (if(vFinalBalance >= 16, KeyEmployee))),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Dentro del Rango',
Count(distinct (if(vFinalBalance >= 0, KeyEmployee))),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Saldo Negativo',
Count(distinct (if(vFinalBalance <0, KeyEmployee))),
)))))

Can anyone guide me on this? I want to allocate per Organizational Unit the number of employees who have a Final Balance in each specified range: 'Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo'.

Thanks in advance

2 Replies
Ksrinivasan
Specialist
Specialist

hi,

some Bracket are missing to calculate, you can try

 IF(((($(Acum.CY) + $(Acum.LY)) - (Sum({<type={'1'}>} distinct Amount)))>='41','Muy Critico','')

and also confirm whether you want to Count of distinct Amount or Sum of distinct Amount,

 

ksrinivasan.

ITMercon
Contributor II
Contributor II
Author

Hey!! thanks a lot for the input, I completed the missing brackets and I fixed the distinct to Amount and it worked properly in one of my columns !! However it's not showing any results for the other columns.

PivotTable BU vs BalanceStatus2.PNG

 

IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Muy Critico',
Count( aggr( (if($(vFinalBalance) >= '41' ,Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Critico',
count( aggr( (if($(vFinalBalance) >= '31',Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Excede el Rango',
count( aggr(distinct (if($(vFinalBalance) >= '16',Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Dentro del Rango',
count( aggr(distinct (if($(vFinalBalance) >= '0',Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Saldo Negativo',
count( aggr(distinct (if($(vFinalBalance) <'0',Amount)),Amount,KeyEmployee,[Business Unit])),
)))))