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: 
Not applicable

Set Analysis: all previous years untill selected month

I want to select a year and a month and I want to see:

- all amounts including the full previous years untill the selected month and year. For example full 2010, full 2011, full 2012, jan 2013 and feb 2013

- all amounts untill selected month year -1. For example  full 2010, full 2011, jan 2012 and feb 2012

I have tried:

sum({$<Year = {"<=$(=max(Year))"},     Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"},>}TOTAL Amount))

But this obviously only gives me the amounts of january and february for all the years.

Any ideas?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try this: sum({$<Year=,Month=,PostingDate={"<=$(=MonthEnd(max(PostingDate)))"}>}TOTAL Amount)

See attached qvw


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
alexandros17
Partner - Champion III
Partner - Champion III

You have to set different variables for each year you want to see and use with set analysis in columns of previous year

sum({$ <year={'$(previousyear)'}> value}

Not applicable
Author

I have tried to use a date field with the monthend function but this didn't give any result

sum

({$<PostingDate={'<=$(=MonthEnd(makedate(Max(Year),max({<Year={$(=max(Year))}>} Month)))'}>}TOTAL Amount)

The MonthEnd function on itself gives a correct date but the result is ' - ' when used in my setanalysis expression

perumal_41
Partner - Specialist II
Partner - Specialist II

HI,

is possible post sample application.

Not applicable
Author

Added my qvw file in first post

Gysbert_Wassenaar

Try this: sum({$<Year=,Month=,PostingDate={"<=$(=MonthEnd(max(PostingDate)))"}>}TOTAL Amount)

See attached qvw


talk is cheap, supply exceeds demand
Not applicable
Author

It works! Thank you very much