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

[Anonymous]

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

[sunny_talwar]

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))

[Ralf_Heukäufer]

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;