Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to get a percent of the total of a calculated dimension in a pivot table.
So, it would be straight-forward if it was a simple dimension used in the expression:
count(distinct ID)/
count(distinct total <Days> ID)
I am using a variable in the calculated dimension called - vAgeingDays, so I was hoping I could use this in the expression ie:
count(distinct ID)/
count(distinct total <$(vAgeingDays)> ID)
OR something similar but it is not working.
Is there perhaps another way to get the total of only the values in this expression(the expression is hiding nulls)
Any ideas?
Many thanks
Null value is causing the issue. Try:
=COUNT(distinct HREC_Ref)/
count(distinct Total if( not isnull(WD_HREC_NoClock), HREC_Ref))
Why it does not work?
Do you have your variable like this
=A
or
A
if you got $ expansion you do not need '=' sign
Feeling Qlikngry?
It depends on how you defined your variable. Could you post that definition?
My variable is defined as :
IF($(vWorkingDays) <=60, dual('0-60', 1),
IF($(vWorkingDays) > 60 and $(vWorkingDays) <= 120, dual('61-120', 2),
IF($(vWorkingDays) > 120 and $(vWorkingDays) <= 180, dual('121-180',3),
IF($(vWorkingDays) > 180 , dual('181+',4),
))))
'Total' can't work on calculated dimension. In other words, 'Total' accepts only script generated field name within < >.
Thanks for the reply Tresesco.
I have now placed this expression into the script - creating a field called WD_HREC_NoClock.
Then I've replaced this as a dimension in place of the previous variable.
I now get 100% for each cell within the pivot table.
Here the dimension WD_HREC_NoClock is called - Time (Days)
COUNT(distinct HREC_Ref)/
count(distinct Total <WD_HREC_NoClock> HREC_Ref)
Try to attach your sample qvw.
If you are having single dimension, you are perhaps over-expressioning it. Try simply:
COUNT(distinct HREC_Ref)/
count(distinct Total <WD_HREC_NoClock> HREC_Ref)
Hi coco,
Please try removing distinct also:
COUNT(HREC_Ref)/
count(Total HREC_Ref)
Regards
KC
Unfortunately nothing seems to be working.
When I use a straight table and use the relative option, I get the correct %.
Would be great if there was a similar option in a pivot table.