topic Re: Cyclic group with 3 month moving/rolling average in QlikView

Cyclic group with 3 month moving/rolling average

sifatnabil — Mon, 22 Dec 2014 04:32:25 GMT

Hi, the 3 month rolling average formula in my chart is:

num(rangeavg(above(sum({<MatchingsMonth=,MatchingsDate=>}

Revenue),0,3)),'#,##0')

I have month and week as a cyclic group in the chart dimension. When I switch to week, I'd need the formula to change to

num(rangeavg(above(sum({<MatchingsMonth=,MatchingsDate=>}

Revenue),0,12)),'#,##0')

How can I automatically change the formula when switching from month to week? Thanks.

tresB — Mon, 22 Dec 2014 04:54:09 GMT

Try like:

If(GetCurrentField(GroupName)='week', <this formula>, <that formula>)

sifatnabil — Tue, 23 Dec 2014 01:07:18 GMT

Thanks. Just FYI it seems to only work with double quotes, i.e. GetCurrentField("GroupName")

tresB — Tue, 23 Dec 2014 04:45:37 GMT

If there is space in between your group name, then only double quotes or squre brackets would be required. Learn about it here: quoteology