topic Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection in QlikView

Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

giovanneb — Wed, 20 Feb 2013 17:55:50 GMT

Hello I have the following situation:

used the following set Analysis:

sum ({$} <Month_Sale = {$(=month(addmonths(max(Date_Sale), -1)))}> Qty)

By using the expression above it works correctly when the selection year is exergar filter to the data of the previous month, my problem happens when I select the month of January because this expression brings zeroed data, how can I resolve this situation? thank you

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Gysbert_Wassenaar — Wed, 20 Feb 2013 18:01:10 GMT

Since you've selected a year you need to exclude selections in the year field in the expression. Otherwise you can't get the Qty from the last month of the previous year, only from the selected year. Assuming your year field is named Year try this:

sum ({$} <Month_Sale = {$(=month(addmonths(max(Date_Sale), -1))), Year=}> Qty)

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

giovanneb — Wed, 20 Feb 2013 18:19:12 GMT

I added the year now when I select the values month are accrued and should not

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Gysbert_Wassenaar — Wed, 20 Feb 2013 18:24:34 GMT

Right you are. Try this then:

sum ({$} <Date_Sale={'=>$(=monthstart(addmonths(max(Date_Sale), -1)))<$(=monthstart(max(Date_Sale)))'},MonthYear=, Year=}> Qty)

You could make things a lot easier for yourself if you start using the qvc library. See here for a tutorial.

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

giovanneb — Wed, 20 Feb 2013 18:42:18 GMT

I tried using the expression presented by you got an error of expression, what could it be? thank you

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Gysbert_Wassenaar — Wed, 20 Feb 2013 18:50:06 GMT

My mistake. A typo. Date_Sale={'=> should be Date_Sale={'>=

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Anonymous — Wed, 20 Feb 2013 18:55:54 GMT

Hi,

Can you please provide test application,it will be works properly no problem.

Regards

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

giovanneb — Wed, 20 Feb 2013 19:28:16 GMT

Hello still can not'll post a sample document QV, I believe I am missing something in the expression

Follow Document

Follow the document would need to analyze that:

Qty Initial Qty Last

Dec 2012 496 0

Jan 2012 1457 496

Feb 2012 1450 1457

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

giovanneb — Wed, 20 Feb 2013 19:29:13 GMT

I'll post a exempl document, thanks

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Gysbert_Wassenaar — Mon, 25 Feb 2013 15:39:40 GMT

If I understand what you want correctly then that's not possible with set analysis. The sets are calculated per chart not per row. First the set is calculated and then the dimensions and expressions are applied to the set. You'll have to use something like AsOf tables. See attached example.

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

giovanneb — Wed, 27 Feb 2013 22:49:27 GMT

Hi, perfect solution, thanks