Rolling period vs previous year - selection issue

[jorgen_santberg]

Hi all,

I was hoping someome can help me with an issue I have.

I want to get the sum of sales in a pivot table for a period based on the selection made in the field MonthYear and I want to get the sum of sales in the same period in the previous year to compare it with. I all goes well until I make a selection in, for example, a particular brand. If I select MonthYear Jan-2017 till May-2017 but the brand is only first sold this year in Mar-2017, than the result in my pivot table shows the sum of sales of Mar-2017 till May-2017 vs. Mar-2016 till May-2016 (regardless any sales in Jan-2016 or Feb-2016).

The expression I use is for the current period =

Sum(Sales)

The expression I use is for the previous period =

Sum({<Year, Month,  MonthYear ={">=$(=Date(addmonths(Min(MonthYear), -12), 'MMMMM-YY'))<=$(=Date(addmonths(Max(MonthYear), -12),  'MMMM-YY'))"}>}Sales)

Thanks in advance!

[sunny_talwar]

Are you interested in YTD for previous year? or would it be based on selection only? May be this

Sum({<Year, Month,  MonthYear ={">=$(=Date(addmonths(Min({<Brand>}MonthYear), -12), 'MMMMM-YY'))<=$(=Date(addmonths(Max(MonthYear), -12),  'MMMM-YY'))"}>}Sales)

[jorgen_santberg]

Hi Sunny,

That would have solved the issue if [Brand] was the only field to select, but it is not. It is also possible to select a productgroup, a specific article, a group of customers, unique customer, et cetera. I could just add all these fields to the expression as in:

Sum({<Year, Month,  MonthYear ={">=$(=Date(addmonths(Min({<Brand, Prodgroup, Article, Customergroup, CustomerID>}MonthYear), -12), 'MMMMM-YY'))<=$(=Date(addmonths(Max(MonthYear), -12),  'MMMM-YY'))"}>}Sales)

but I was hoping for an easier and more esthetic solution.

[sunny_talwar · Accepted solution]

What could be easier? Do you have more fields where you don't want selections to impact then the fields which should impact? If that's true you can try this

Sum({<Year, Month,  MonthYear ={">=$(=Date(addmonths(Min({1<Field1 = p(Field1), Field2 = p(Field2)>}MonthYear), -12), 'MMMMM-YY'))<=$(=Date(addmonths(Max(MonthYear), -12),  'MMMM-YY'))"}>}Sales)

[jorgen_santberg]

That did the trick, thanks!