Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jorgen_santberg
New Contributor II

Rolling period vs previous year - selection issue

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!

1 Solution

Accepted Solutions

Re: Rolling period vs previous year - selection issue

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)

4 Replies

Re: Rolling period vs previous year - selection issue

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
New Contributor II

Re: Rolling period vs previous year - selection issue

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.

Re: Rolling period vs previous year - selection issue

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
New Contributor II

Re: Rolling period vs previous year - selection issue

That did the trick, thanks!