topic Re: Rolling period vs previous year - selection issue in QlikView

Rolling period vs previous year - selection issue

jorgen_santberg — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Rolling period vs previous year - selection issue

sunny_talwar — Tue, 06 Jun 2017 15:15:15 GMT

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)

Re: Rolling period vs previous year - selection issue

jorgen_santberg — Tue, 06 Jun 2017 19:44:40 GMT

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

sunny_talwar — Tue, 06 Jun 2017 19:46:50 GMT

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)

Re: Rolling period vs previous year - selection issue

jorgen_santberg — Tue, 06 Jun 2017 20:16:13 GMT

That did the trick, thanks!