Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
krishna4bo
Creator
Creator

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

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)
Jan23
Feb34
Mar77
Apr206
May132
Jun47
Total 519

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

Regards,

Krishna

2 Replies
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
Partner
Partner

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;