Hello,
I'm trying to dynamically set the color range of a map so that the middle value of the gradient is equal to the average for all observations. The color by equation feature only allows me to set the top and the bottom of the range, so I'm doing this as follows:
Top of Color Range:
measure + RangeMax(
Max(aggr(measure,dimension)) - measure,
measure - Min(aggr(measure,dimension)))
Bottom of Color Range:
measure - RangeMax(
Max(aggr(measure,dimension)) - measure,
measure - Min(aggr(measure,dimension)))
Is there a function within Qlik Sense that does the same thing? Because the measure contains a $-expansion in the set analysis, I can't simply define the measure as a variable. As an example, the measure calculation in one of the views is:
Sum({<CTD_Key={$(=vCTD_Num)},
Segment_Key=($(=Only(pSeg_Num)))>}Sales)
/Sum{<CTD_Key={$(=vCTD_Denom)},
Segment_Key={$(=Only(pSeg_Denom))}>}Sales)
Since the measure calculation appears 5 times in the color range limits equation, it's becoming overly complicated and with a lot of duplication.
Sum({<CTD_Key={$(=vCTD_Num)},
Segment_Key=($(=Only(pSeg_Num)))>}Sales)
/Sum{<CTD_Key={$(=vCTD_Denom)},
Segment_Key={$(=Only(pSeg_Denom))}>}Sales)
+ RangeMax(
Max(aggr(
Sum({<CTD_Key={$(=vCTD_Num)},
Segment_Key=($(=Only(pSeg_Num)))>}Sales)
/Sum{<CTD_Key={$(=vCTD_Denom)},
Segment_Key={$(=Only(pSeg_Denom))}>}Sales)
,dimension)) -
Sum({<CTD_Key={$(=vCTD_Num)},
Segment_Key=($(=Only(pSeg_Num)))>}Sales)
/Sum{<CTD_Key={$(=vCTD_Denom)},
Segment_Key={$(=Only(pSeg_Denom))}>}Sales)
,
Sum({<CTD_Key={$(=vCTD_Num)},
Segment_Key=($(=Only(pSeg_Num)))>}Sales)
/Sum{<CTD_Key={$(=vCTD_Denom)},
Segment_Key={$(=Only(pSeg_Denom))}>}Sales)
- Min(aggr(
Sum({<CTD_Key={$(=vCTD_Num)},
Segment_Key=($(=Only(pSeg_Num)))>}Sales)
/Sum{<CTD_Key={$(=vCTD_Denom)},
Segment_Key={$(=Only(pSeg_Denom))}>}Sales)
,dimension)))
Thanks for any help or alternative means to the same end.
Best regards,
Frank
