Using variables in Expression

[#1]

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

[jerem1234 · Accepted solution]

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!

[#3]

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

[jerem1234]

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!

[jaimeaguilar]

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

[PrashantSangle]

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,