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: 
qvhjenvo
Contributor
Contributor

Sum for last three end of years

Hi,

I have a calendar that consists of month,year and monthyear.

Skjermbilde.PNG

I want to calculate a sum that show selected month and the end of the last three years.

That means (20151231, 20161231, 20171231 and max(monthyear). 2018). Just for the end of desembers of these years.

My expression:

sum({<

year = {">=$(=year(date(max(yearend(monthyear)), 'YYYYMM' ))-3)<= $(=year(date(max(addmonths(monthyear,1)), 'YYYYMM' )))"}

>} sales)

It does not work.

Some hints?

Thanks in advance.

/Loi

5 Replies
bramkn
Partner - Specialist
Partner - Specialist

The question is nog 100% clear to me.

You want sales of dec 2015/2016/2017 and the sales of oktober 2018?

or do you want the sales of 2015/2016/2017 and the sales untill oktober 2018?

qvhjenvo
Contributor
Contributor
Author

H

I want sales of end dec 2015/2016/2017 and the sales of oktober 2018.


/Loi

bramkn
Partner - Specialist
Partner - Specialist

the end of dec? meaning the last day of december. the whole month december or the sales for the whole year to the last date of dec?

qvhjenvo
Contributor
Contributor
Author

Hi,

the end of dec? meaning the last day of december. Yes.


the whole month december or the sales for the whole year to the last date of dec? No

My data is based on balance at end of month.


Loi

bramkn
Partner - Specialist
Partner - Specialist

That explains a lot.

just do the set anaylysis and put in the dates. the last day of a year always has the same date(apart from the year) so just sum and then use comasepration to fill in the dates. and also add the last date of this year(so i am guessing today()-1.