Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I used a calculated dimension in the gauge where it buckets the number of days between 2 dates, analyzed against no of documents completed. I need to color the gauge based on these buckets, where I enabled the color code option under the colors and legend, but it does not work. The values are correct though. My syntax is:
If(round(enddate-startdate) <= 30, 'green',
If(round(enddate-startdate) <= 60, 'orange',
If(round(enddate-startdate) > 60, 'red',
'black'
)))
I am not sure why it fail. In particular, those less than 30 days do not seem to be captured, and turn out black which is my catch all condition. Those bigger than 30 are captured correctly. Am very puzzled, please help
Can you post an example qlik sense app that demonstrates the problem?
where's your aggregation function? shouldn't gauges show Measures (=results of aggregations [sum, min, max, avg,count,... ])
I am referring to the custom color expression under colors and legend for the gauge. The dimension is a calculated one similar to the coding above except that the rgb are replaced with labels, for example
If(round(enddate-startdate) <= 30, 'Less than a month'
The measure is simply counting number of documents
The gauge is showimg the right values, just that the color coding is not working. I cannot use the default color by dimension option as there are specific colors i need to use
again, where's your aggregation function? a gauge shows a measure, it doesn't use one ore more dimensions.
if you want to cheat the gauge, be prepared for some advanced usage of the AGGR function (hint: look this up, really, it still is a mystery to me in some aspects sometimes)
what you want to achieve is actually natively done by a stacked bar chart.
Example to show the numer of customers that bought more than 25.000;
as calculated dimension, don't forget to AGGR:
=aggr(if(Sum ([Sales Amount])>25000,1,0),Customer) (Dimension Value 1 for customers >25k)
Measure: count(Customer)
If you really want to achieve something similar in a gauge:
Measure:
count(Customer)/count(total Customer) (in order to show %)
Segment Definition:
=sum(aggr(if(Sum([Sales Amount])>25000,1,0),Customer))/count(total Customer) (to show the % of customers >25k)
have fun adapting these examples for your case;
Please share data in excel sheet.
Thanks.