topic Formula Help Needed in QlikView

Formula Help Needed

JustinRamsey — Wed, 08 Apr 2020 18:37:07 GMT

Good Afternoon,

I am in need of your alls guidance once again with help for a formula. I have a field MonthConsumed that is a date field, a part number MATNR, and field S031.SPMON that is the total consumed in that month. I am trying to create within a table a formula that would average the last 6 months worth of consumption. Logically it would be S031.SPMON where MonthConsumed = today() and then again for today()-30 for last month etc for 6 months. Can someone help with this formula I cannot seem to get it to work at all.

Re: Formula Help Needed

JustinRamsey — Wed, 08 Apr 2020 18:37:50 GMT

Re: Formula Help Needed

Brett_Bleess — Wed, 29 Apr 2020 17:40:00 GMT

Have a look at the following Design Blog post, may be of some use to you:

https://community.qlik.com/t5/Qlik-Design-Blog/Average-Which-average/ba-p/1466654

If you want to search further in this area, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

You may actually need the following Set Analysis post, there are a couple other related links at the bottom of this post too:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

Re: Formula Help Needed

Saravanan_Desingh — Fri, 01 May 2020 00:41:10 GMT

Can you try this?

Avg(If(MonthConsumed>=AddMonths(Today(),-6), S031.SPMON))

Re: Formula Help Needed

JustinRamsey — Fri, 01 May 2020 12:57:04 GMT

I appreciate the help. Unfortunately it didnt work .

Re: Formula Help Needed

Brett_Bleess — Tue, 12 May 2020 15:00:42 GMT

May need to attach a sample app on this one, as there has to be something we are missing in the details I believe, likely only way to get more responses.

Regards,
Brett