Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
MVP
MVP

Re: Variable in <> total

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
Highlighted

Re: Variable in <> total

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

Highlighted
MVP
MVP

Re: Variable in <> total

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

Highlighted
Not applicable

Re: Variable in <> total

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

))))

Highlighted
MVP
MVP

Re: Variable in <> total

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

Highlighted
Not applicable

Re: Variable in <> total

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)

Highlighted
MVP
MVP

Re: Variable in <> total

Try to attach your sample qvw.

Highlighted
MVP
MVP

Re: Variable in <> total

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)

Highlighted
Honored Contributor II

Re: Variable in <> total

Hi coco,

Please try removing distinct also:

COUNT(HREC_Ref)/

count(Total HREC_Ref)

Regards

KC

Best Regards,
KC
Highlighted
Not applicable

Re: Variable in <> total

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.