Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th 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;