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

[tresesco]

Could you post a representative sample qvw?

[#3]

Hi Tresesco.

I've attached a sample .qvw.

You will notice the issue in the table and chart.

Thanks !

[tresesco · Accepted solution]

Null value is causing the issue. Try:

=COUNT(distinct HREC_Ref)/

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

[#5]

Thanks Tresesco.