Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
s_herrera_pugli
Partner - Contributor
Partner - Contributor

avg expression of specific months

Hi everybody

im working on a dashboard which has "YearMonth" field from the data source (month+year), i want to calculate a percentage based on the average of "sum([VISITS])" of months may-18, june-18 and july-18 and compare them with august-18.

I wanna know if there was a growth or not. How do i "separate" the months to select JUST the ones i want?

Thanks in advance

Labels (8)
4 Replies
bharatchauhan
Contributor II
Contributor II

Based on the requirement provided by you.
If the yearmonth field have values like 201805,201806,201807,201808.
Expression of growth would be
=(sum({<yearmonth={"=max(yearmonth)"}>}VISITS)
/
(Sum({<yearmonth={">=max(yearmonth)-3 <max(yearmonth)"}>}VISITS)/3)
)-1
s_herrera_pugli
Partner - Contributor
Partner - Contributor
Author

Hi,  it didn't work, when I use your formula it brings me back "1" as the result. I attach you an image of the "yearmonth" list

 

bharatchauhan
Contributor II
Contributor II

Hi,

Try this one

((sum({<yearmonth={"=DATE(max(DATE(DATE#(yearmonth,'YYYY-MMM'))),'YYYY-MMM')"}>}VISITS) / (Sum({<yearmonth={">=DATE(max(DATE(DATE#(yearmonth,'YYYY-MMM')))-3 ,'YYYY-MMM')<DATE(max(DATE(DATE#(yearmonth,'YYYY-MMM'))),'YYYY-MMM')"}>}VISITS)/3) )-1)*100

s_herrera_pugli
Partner - Contributor
Partner - Contributor
Author

Thank you, with little changes i could make it work!