10 Replies Latest reply: Feb 27, 2013 5:49 PM by Giovanne Bergstein

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

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

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

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

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

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

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

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

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

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

Hi, perfect solution, thanks

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

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

I'll post a exempl document, thanks