topic RangeAvg function in QlikView

RangeAvg function

bobbydave — Fri, 20 Nov 2020 18:02:30 GMT

Hey guys,

I am trying to use the RangeAvg function to create an alert and should look like something below which one of you guys has already helped me out with. You think it would be easy that I could just replace [Record Count] with

(sum([Record Count]) - rangeavg(after(sum([Record Count]),1,6))) / rangeavg(after(sum([Record Count]),1,6))

Concat(
            if(
                aggr(sum({<[Control Description]={"Claim Payment"},
                                    [Fiscal Year Month] = {"2015-12" }>}
                                    [Record Count]),
                                    Country)=0,
                                   Country & ': Claim Payment is reported as zero.' & chr(10))
          )

I want to send an alert if the above expression < 0% or over 50%. I tried using if statements

if((sum([Record Count]) - rangeavg(after(sum([Record Count]),1,6)))
/ rangeavg(after(sum([Record Count]),1,6)) < '0%'

or

(sum([Record Count]) - rangeavg(after(sum([Record Count]),1,6)))
/ rangeavg(after(sum([Record Count]),1,6)) > '50%'
,Country
)

Any help appreciated.

Re: RangeAvg function

Oleg_Troyansky — Fri, 22 Jan 2016 14:26:34 GMT

Hi Bobby (or Dave?)

I think it would be a lot easier to troubleshoot if you posted a sample app with your calculation.

Just by looking at the formulas, I suspect that the problem is caused by using the function After(). This is a Chart function that cannot be used outside of charts (i.e. in an Alert). The term After() pertains to the following row in the chart. Outside of the chart, the function is meaningless.

That's just a wild guess by looking at your rather complex formulas. If you'd like anyone to do any further analysis, please post a sample.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: RangeAvg function

Fri, 22 Jan 2016 14:40:16 GMT

You can't use AFTER function out side of the chart object.

Instead of writing the complex UI expressions, calculate the expressions in the script level and create new table with flag. Based on the flag you can directly trigger the Alert.