Comparing values in a table

[AzuEnM]

Hey everyone!

I have a little porblem with the analysis of data in a pivot table.

Here is an example, we are talking about data of an electricity meter:

I am currently trying to highlight the values that are significantly higher or lower than average.

Therefore I am trying to build a formula for the conditional formatting of the background color.

I already tried to do it with the Median, but that did not work (Qlik Sense calculatd only the median of the specific day, not of a longer period of days):

If(Sum({<Meternumber={'123-4567891'}>}Meterdata)>(Median(Aggr(Sum({<Meternumber={'123-4567891'}>}Meterdata),Date))*2.0), ARGB(75,255,0,0))

Is there anybody who knows a way to fix this formula?

If there is a way to make it work with the average of a period or a comparison to the data of the previous day I would be happy as well.

Thank you very much in advance!

[CezarioAbrantes · Accepted solution]

Hi,

First of all, I recommend you to create a master item of the expression Sum({<Meternumber={'123-4567891'}>}Meterdata) and use the name of the master item instead the expression in the following calcs.

You can obtain the Average of the line in Pivot Table doing the following:

=Avg(Total <Date>

Aggr(

    [Your Master Measure]

    ,Date,YourRowDimension)

)

You can also get the result of the previus day doing this:

=Before([Your Master Item])

Regards

Cezário

[AzuEnM]

Hi Cezário,

that worked great!

The "Total"-function was the key to success.

Thank you very much!