Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Variable in <> total

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Null value is causing the issue. Try:

=COUNT(distinct HREC_Ref)/

count(distinct Total if( not isnull(WD_HREC_NoClock),  HREC_Ref))

View solution in original post

13 Replies
robert_mika
Master III
Master III

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?

How To /Missing Manual(25 articles).

tresesco
MVP
MVP

It depends on how you defined your variable. Could you post that definition?

Not applicable
Author

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

))))

tresesco
MVP
MVP

'Total' can't work on calculated dimension. In other words, 'Total' accepts only script generated field name within < >.

Not applicable
Author

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)

DELETE.png

COUNT(distinct HREC_Ref)/

count(distinct Total <WD_HREC_NoClock> HREC_Ref)

tresesco
MVP
MVP

Try to attach your sample qvw.

tresesco
MVP
MVP

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)

jyothish8807
Master II
Master II

Hi coco,

Please try removing distinct also:

COUNT(HREC_Ref)/

count(Total HREC_Ref)

Regards

KC

Best Regards,
KC
Not applicable
Author

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.