topic Re: last 13 months data in QlikView

last 13 months data

ajaykumar1 — Tue, 04 Feb 2014 17:03:45 GMT

Hi All,

1.How we can get the last 13 months data with out writing the 13 expressions in chart.

stuck some where to write the variable for 13 months and pass that to set analysis level.The field is 'Month' which is having around 20+ months data.

Thanks,

Ajay

Re: last 13 months data

Tue, 04 Feb 2014 17:13:39 GMT

Hi, you should create a calendar, assigning a progressive id to every month, so that you have a key to identify each month.

then you can just insert

if(Month_ID > Max(Month_ID)-13, Month) as your dimension

Re: last 13 months data

ajaykumar1 — Tue, 04 Feb 2014 17:20:21 GMT

Thanks Luca...

let me try the same

Re: last 13 months data

ajaykumar1 — Tue, 04 Feb 2014 17:35:32 GMT

Hi its not working.

load Month,

sal,

if(Month>max(Month)-13,Month) as month_derived,

Re: last 13 months data

Tue, 04 Feb 2014 17:50:43 GMT

Any one is there ????

Re: last 13 months data

santharubban — Tue, 04 Feb 2014 17:51:39 GMT

try this, AddMonths(MonthStart(Today()), -14) in dimension of chart.

load Month,

sal,

month_derived from xxxx.qvd where date(month_derived) => AddMonths(MonthStart(Today()), -14) ;

Re: last 13 months data

Tue, 04 Feb 2014 17:56:38 GMT

No Luck Santhu.....

Re: last 13 months data

santharubban — Tue, 04 Feb 2014 18:00:14 GMT

what error ur getting?

Re: last 13 months data

ajaykumar1 — Tue, 04 Feb 2014 18:07:51 GMT

I am getting only one record.... am trying to attach the qvw but am unable.

Month	sal	type
01/01/12	100	test
01/02/13	101	manf
01/03/14	102	tetst
01/04/15	103	hvv
01/05/16	104	vshvv
01/06/17	105	vvZ
01/07/18	106	vsv/
01/08/11	107	sgvugu
01/09/10	108	vslvhkls
01/10/09	a	ns;v
01/11/08	110	vsdhv
01/12/01	111	sgvugu
01/13/02	112	svdi
01/14/04	113	sdv;do
01/15/87	114	svdiwhp
01/16/86	115	shvsh;
01/17/85	116	sv;sv
01/18/80	117	vsh;v
01/19/12	118	sbh;bj'
01/20/12	119	ah;adoh
01/21/12	120	ab'adj'

Re: last 13 months data

Carlos_Reyes — Tue, 04 Feb 2014 19:03:43 GMT

I always use a MonthName field for this type of analysis. Review the attached to see an example.

Re: last 13 months data

Wed, 05 Feb 2014 05:07:42 GMT

hi,

can any body post the set analysis expression for this .

regards

Mahesh

Re: last 13 months data

sudeepkm — Wed, 05 Feb 2014 06:06:39 GMT

As Luca suggested you can give ID or number to your Months like below. Assume that your date field name is "Period"

num(Month(Period)) + if(year(Period)=Year(Today()),12,0) as MonthNum, // Use this in your master calendar script

Then you can create two variables to hold the values of the max (MonthNum) and max(MonthNum,12) like below

vMaxMonNum = max(MonthNum)

vMaxMonNum12 = max(MonthNum,12)

Then in your chart dimension use the given below: Assume that you want to show the MonthYear in the dimension then you can write something like this.

=if(MonthNum>vMaxMonNum13 and MonthNum<=vMaxMonNum,MonYr)

If you want to do it in expression then you can use Set Analysis to achieve this: Multiple expressions starting from

sum({< Period={"$(=max(Period))"} >}Amount) // latest month

till

sum({< MonStart={"$(=Num(MonthStart(max(Period),-12)))"} >} Amount)

Re: last 13 months data

Carlos_Reyes — Wed, 05 Feb 2014 11:50:07 GMT

I already did, review my example.