Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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?

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Set Analysis: all previous years untill selected month

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

See attached qvw


talk is cheap, supply exceeds demand
6 Replies

Re: Set Analysis: all previous years untill selected month

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

Re: Set Analysis: all previous years untill selected month

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

Partner
Partner

Re: Set Analysis: all previous years untill selected month

HI,

is possible post sample application.

Not applicable

Re: Set Analysis: all previous years untill selected month

Added my qvw file in first post

MVP & Luminary
MVP & Luminary

Re: Set Analysis: all previous years untill selected month

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

See attached qvw


talk is cheap, supply exceeds demand
Not applicable

Re: Set Analysis: all previous years untill selected month

It works! Thank you very much