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

[giovanneb]

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

[Gysbert_Wassenaar]

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)

[giovanneb]

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

[Gysbert_Wassenaar]

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.

[giovanneb]

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

[Gysbert_Wassenaar]

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

[Anonymous]

Hi,

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

Regards

[giovanneb]

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

[giovanneb]

I'll post a exempl document, thanks

[Gysbert_Wassenaar · Accepted solution]

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.