topic Re: Displaying data for last 12 monthsHi, in QlikView

Displaying data for last 12 monthsHi,

Thu, 19 Dec 2013 07:28:40 GMT

Hi,

I have a requirement wherein the metric data corresponding to month must be displayed through two charts each chart displays the data for 6 month sorted as current month-1, current month-2.... to current month-12.So when I try displaying value for current month-11 and current month-12 data (i.e., data for Jan and Dec) I am getting the data displayed for Jan and Dec of current year and not the previous year. So can anyone please let me know how exactly I would be able to get data for current month-11 of current year i.e., data for Jan 2012 in the chart.

I have attached the application. Please let me know how this can achieved

Re: Displaying data for last 12 monthsHi,

sudeepkm — Thu, 19 Dec 2013 09:24:47 GMT

I think you can follow the below given steps to create a Year over Year metrics.

1. Since you have Month and Year so you can create a date field using makedate()

Create an inline table like below:

monthmap:

Mapping load * Inline [

MonthNames,monno

JAN,1

FEB,2

MAR,3

APR,4

MAY,5

JUN,6

JUL,7

AUG,8

SEP,9

OCT,10

NOV,11

DEC,12

];

2. Since you have 2012 and 2013 as values of Year field so you can create a Date field by passing Year, Month and 1 like below:

MakeDate(Year,ApplyMap('monthmap',upper(trim(Month))),1) as dt

3. Now in your chart you can use it like below to create the Year over Year

Dimension: Metric Name

Expression 1 Level: =MonthName(MonthStart(max(dt),-12))

Expression 1 Defn: sum({<Year=,Month=, dt={"$(=MonthStart(max(dt),-12))"} >} Value)

Expression 2 Level: =MonthName(MonthStart(max(dt),-11))

Expression 2 Defn: sum({<Year=,Month=, dt={"$(=MonthStart(max(dt),-11))"} >} Value)

continue creating expressions until

=MonthName(MonthStart(max(dt),-1))

=sum({<Year=,Month=, dt={"$(=MonthStart(max(dt),-1))"} >} Value)

=MonthName(MonthStart(max(dt)))

=sum({<Year=,Month=, dt={"$(=MonthStart(max(dt)))"} >} Value)

Your chart will show you 13 months rolling. Dec 2012 till Dec 2013.

Please find the attached QVW for details.

Re: Displaying data for last 12 monthsHi,

sudeepkm — Thu, 19 Dec 2013 09:26:55 GMT

sorry I forgot to attach the QVW

Re: Displaying data for last 12 monthsHi,

Thu, 19 Dec 2013 10:15:11 GMT

I do not want to make any changes to the script. Is it not possible to achieve the objective without making any changes to the script?

Thanks & Regards,

Anushree Shetty