topic Set Analysis: all previous years untill selected month in QlikView

Set Analysis: all previous years untill selected month

Tue, 19 Feb 2013 10:30:49 GMT

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?

Re: Set Analysis: all previous years untill selected month

alexandros17 — Tue, 19 Feb 2013 10:33:30 GMT

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}

Re: Set Analysis: all previous years untill selected month

Tue, 19 Feb 2013 11:25:25 GMT

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

Re: Set Analysis: all previous years untill selected month

perumal_41 — Tue, 19 Feb 2013 11:30:45 GMT

HI,

is possible post sample application.

Re: Set Analysis: all previous years untill selected month

Tue, 19 Feb 2013 13:17:42 GMT

Added my qvw file in first post

Re: Set Analysis: all previous years untill selected month

Gysbert_Wassenaar — Tue, 19 Feb 2013 13:21:44 GMT

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

See attached qvw

Re: Set Analysis: all previous years untill selected month

Tue, 19 Feb 2013 14:35:01 GMT

It works! Thank you very much