topic Re: Variable in <> total in QlikView

Variable in <> total

Tue, 22 Dec 2015 04:38:00 GMT

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

Re: Variable in <> total

robert_mika — Tue, 22 Dec 2015 05:44:40 GMT

Why it does not work?

Do you have your variable like this

if you got $ expansion you do not need '=' sign

Feeling Qlikngry?

How To /Missing Manual(25 articles).

Re: Variable in <> total

tresB — Tue, 22 Dec 2015 05:50:46 GMT

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

Re: Variable in <> total

Tue, 22 Dec 2015 07:25:38 GMT

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

))))

Re: Variable in <> total

tresB — Tue, 22 Dec 2015 07:33:30 GMT

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

Re: Variable in <> total

Tue, 22 Dec 2015 07:59:57 GMT

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)

Re: Variable in <> total

tresB — Tue, 22 Dec 2015 08:32:23 GMT

Try to attach your sample qvw.

Re: Variable in <> total

tresB — Tue, 22 Dec 2015 09:16:51 GMT

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)

Re: Variable in <> total

jyothish8807 — Tue, 22 Dec 2015 09:28:03 GMT

Hi coco,

Please try removing distinct also:

COUNT(HREC_Ref)/

count(Total HREC_Ref)

Regards

Re: Variable in <> total

Wed, 23 Dec 2015 11:15:08 GMT

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.

Re: Variable in <> total

tresB — Thu, 24 Dec 2015 04:23:19 GMT

Could you post a representative sample qvw?

Re: Variable in <> total

Thu, 24 Dec 2015 04:56:17 GMT

Hi Tresesco.

I've attached a sample .qvw.

You will notice the issue in the table and chart.

Thanks !

Re: Variable in <> total

tresB — Thu, 24 Dec 2015 06:07:43 GMT

Null value is causing the issue. Try:

=COUNT(distinct HREC_Ref)/

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

Re: Variable in <> total

Sat, 26 Dec 2015 00:02:14 GMT

Thanks Tresesco.