Variable in <> total

[#1]

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

[robert_mika]

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]

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

[#4]

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]

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

[#6]

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)

[tresesco]

Try to attach your sample qvw.

[tresesco]

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]

Hi coco,

Please try removing distinct also:

COUNT(HREC_Ref)/

count(Total HREC_Ref)

Regards

KC

[#10]

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.