Background Color Expression And Set Analysis

[Anonymous]

I am working with the Background Color Expression and having issues with the following calculation. I validated the results of each and they work as expected. The issue is when trying to highlight those values in the dataset below the average of the set. If I set a fixed value it works if i apply the validated calculation it does not recognize the value. Assume this a quote or single quote issue to pass the value into the if statement?

IF(sum({<CalWeekStart = {">=$(=WeekStart(Today(),-14))<=$(=WeekStart(Today(),-2))"}>} Shipments)
<
28000 ///Set value works
//AVG(AGGR( sum({<CalWeekStart = {'>=$(=WeekStart(Today(),-14))<=$(=WeekStart(Today(),-2))'}>} Shipments) ,CalWeekStart)) /// Set Analysis Aggr Does Not

,'#FFCCBC')

[sbaro_bd]

@Anonymous ,

If you isolate your AVG() expression in a KPI, which result do you obtain? If the expression works correctly, you have to obtain a single value.

Regards.

[Anonymous]

The set analysis and aggregation expression is returning a single value and was validated. So this is not an answer. Thank you.

[sbaro_bd]

@Anonymous ,

If you can, provide us an app with this use case for a better help.

Regards.

[Anonymous]

For those who are familiar with setting up set analysis and aggr in QlikView and have migrated to Qlik Sense please just try to apply the Background Color Expression in your app and put your findings here. Sharing the app shouldn't be needed for a straight forward example. sbaro_bd thank you for your input but would like other user feedback here.

[Aditya_Chitale]

@Anonymous ,

That's because, you are trying to compare the expression with only one avg aggregated value (In your case, 28000). whereas if you apply the same expression on a table, it yields results on a row level.

Try adding 'total' in your avg expression to get consistent avg numbers accross all rows.

something like this:

AVG(Total AGGR( sum({<CalWeekStart = {'>=$(=WeekStart(Today(),-14))<=$(=WeekStart(Today(),-2))'}>} Shipments) ,CalWeekStart))

Regards,

Aditya