topic Using variables in Expression in QlikView

Using variables in Expression

Thu, 10 Apr 2014 15:12:21 GMT

Greetings

I have created a variable

vDevices=COUNT(DISTINCT {1} DEVICE_ID)

Top Chart

---------------

Suppose if I substitute the expression as below

=count(DISTINCT if(AVERAGE_JAM >=0, DEVICE_ID))/(COUNT(DISTINCT {1} DEVICE_ID))

I am not getting the correct chart

Bottom Chart(OK)

-------------------------

While generating chart the expression contains as below

=count(DISTINCT if(AVERAGE_JAM >=0, DEVICE_ID))/$(vDevices)

This gives the correct result

Please refer to the chart

What mistake I am making?

Thanks

Re: Using variables in Expression

jerem1234 — Thu, 10 Apr 2014 15:28:14 GMT

You need to add total to your count expression like:

=count(DISTINCT if(AVERAGE_JAM >=0, DEVICE_ID))/(COUNT(DISTINCT TOTAL {1} DEVICE_ID))

Hope this helps!

Re: Using variables in Expression

Thu, 10 Apr 2014 15:30:43 GMT

Thanks Jerem. Appreciate. But while using variable it is working without TOTAL. Do you know why?

Re: Using variables in Expression

jerem1234 — Thu, 10 Apr 2014 15:36:39 GMT

It is because of the dimension on the chart. The {1} ignores selections, but does not ignore the dimensions in the chart. Therefore, you need to use total to ignore that dimension to get the total amount of devices. It works in the variable, because it is calculating it BEFORE you use it in chart, and therefore does not have an dimensions aggregating it. You can see for yourself by going into your variable and deleting the "=" sign. The chart you have for the variable will now be wrong. Without the "=" sign, the expression in the variable is calculated in the chart and not before.

Hope this helps!

Re: Using variables in Expression

jaimeaguilar — Thu, 10 Apr 2014 15:40:02 GMT

Hi,

It has to do with dimensionality. If you use the expression without a variable --> =count(DISTINCT if(AVERAGE_JAM >=0, DEVICE_ID))/(COUNT(DISTINCT {1} DEVICE_ID)), the expression will be calculated once for all the dimension

On the other hand, if you store =COUNT(DISTINCT {1} DEVICE_ID) in a variable, this part of the expression will be calculated for every dimension value.

You can also try something like this:

count(DISTINCT if(AVERAGE_JAM >=0, DEVICE_ID))/$(=COUNT(DISTINCT TOTAL {1} DEVICE_ID)) that would be like using a local variable,

regards

Re: Using variables in Expression

PrashantSangle — Thu, 10 Apr 2014 16:01:41 GMT

Hi,

vDevices=COUNT(DISTINCT {1} DEVICE_ID)

your variable gives you the overall total ignoring all dimension but

but

count(DISTINCT if(AVERAGE_JAM >=0, DEVICE_ID))/(COUNT(DISTINCT {1} DEVICE_ID))

when you use same expression in your chart it is evaluated with respect to each dimension that why you are getting 100%

and that's why you have to use TOTAL in your expression as suggested by jerem1234

Regards,