Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hii All,
i have one pivot table with two measures(sales1, sales2) and monthyear field('MMM-YYYY')
like this...
Jan-2013 Feb-2013 Mar-2013.................Nov-2016
sales1 10 20 30 30
sales2 15 18 24 25
and i have taken two list boxes of month and year...
so here my requirement is if i select any month and year, pivot table should display the monthyear of 12 months before that particular selected month and year like this
ex:
i have selected mar-2015 in list box,then my pivot table should be like this
Mar-2014 Apr-2014 May-2014.................Jan-2015 Feb-2015 Mar-2015
sales1 23 12 23 56 78 45
sales2 34 56 67 89 67 45
could u plz help me
If you have a date field in your model then you can use that to filter the last 13 months. If you don't have a date field, but only a year and a month field then you need to create a date field from those fields: Num(Date#(monthyear, 'MMM-YYYY')) as MyNumericDateField.
Once you have that you can change your expressions from sum(sales1) to sum({<year=,month=,monthyear=,MyNumericDateField={">=$(=AddMonths(Max(MyNumericDateField),-13))<=$(=Max(MyNumericDateField))"}>}sales1)
If you have problems getting this to work then please post a small qlikview document with example data that demonstrates the problem.