topic calculate the AVG difference count between previous month and current month for last 6 Months. in QlikView

calculate the AVG difference count between previous month and current month for last 6 Months.

Anonymous — Thu, 21 Jun 2018 12:36:21 GMT

Hi All,

I need to calculate the AVG difference count between previous month and current month for last 6 Months.

=Month(sys_created_on)	count({<sys_created_on={">=$(=MonthStart(Max(sys_created_on),-5))<=$(=Date(Max(sys_created_on)))"}>}number)
Jan	23
Feb	34
Mar	77
Apr	206
May	132
Jun	47
Total	519

KPI: ((23-34)+(34-77)+(77-206)+(206-132)+(132-47))/5

Regards,

Krishna

Re: calculate the AVG difference count between previous month and current month for last 6 Months.

sunny_talwar — Thu, 21 Jun 2018 12:48:36 GMT

May be this?

=Avg({<sys_created_on={">=$(=MonthStart(Max(sys_created_on),-5))<=$(=Date(Max(sys_created_on)))"}>} Aggr(Count({<sys_created_on={">=$(=MonthStart(Max(sys_created_on),-5))<=$(=Date(Max(sys_created_on)))"}>}number) -

Below(Count({<sys_created_on={">=$(=MonthStart(Max(sys_created_on),-5))<=$(=Date(Max(sys_created_on)))"}>}number)), Month))

Re: calculate the AVG difference count between previous month and current month for last 6 Months.

Ralf_Heukäufer — Thu, 21 Jun 2018 13:08:20 GMT

Hello Krishna,

you can solve that in more than one way here is one of them:

First you need to create a Month field in your load

YourTable:

Load

"sys_created_on",

month("sys_created_on") as Month,

number

from.......;

After that you can make a calculation in a resident load of your table:

HelpTable:

load

Month,

sum(number) as number,

resident YourTable group by Month desc;

And another resident Load for the last 6 Month

load

((peek(number,1,HelpTable)-peek(number,0,HelpTable))+(peek(number,2,HelpTable)-peek(number,1,HelpTable))+(peek(number,3,HelpTable)-peek(number,2,HelpTable))+(peek(number,4,HelpTable)-peek(number,3,HelpTable))+(peek(number,5,HelpTable)-peek(number,4,HelpTable)))/5 as AVG6MONTH

from HelpTable;