topic Re: Month Order in QlikView

Month Order

upaliwije — Sun, 13 Apr 2014 02:05:33 GMT

Hi Friends

I have attached a QVW file and the supporting work sheet. I want your help to do the following

1.Create master Calender with Year_Month as field

2. When I select an Year_Month that particular month should be the 1st Column of the Pivot table other months after that

Eg : If I select 2013_05 as Year_Month my pivot table should look like as follows

YEAR	MONTH	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR
2013	1	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,139,520	6,129,330	4,060,620
2013	2	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	6,129,330	4,060,620
2013	3	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	4,060,620
2013	4	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	5	5,017,381	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	6	5,017,381	3,978,719	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	7	5,017,381	3,978,719	4,813,178	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	8	5,017,381	3,978,719	4,813,178	4,772,933	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	9	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	10	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	11	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	12	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	3,715,368	4,122,270	5,225,041	5,436,199	3,911,853
2013	Total	61,470,245	51,743,404	######	######	59,315,184	######	64,407,304	######	49,467,242	62,614,973	66,620,652	47,388,537

Pls help me to solve the issue. If You can write the script in my document so much the better

Thanks in advance

Re: Month Order

Sun, 13 Apr 2014 14:11:09 GMT

Hello,

i'am not sure what are your real source data and what it was done with those data in your excel.

But if your excel crosstable is your source, you may do it for example this way.

Let me know if you like it and if you need more help

regads

Darek

Re: Month Order

its_anandrjs — Sun, 13 Apr 2014 14:43:41 GMT

As to see your data from attached excel file you have to load a cross table for this because there is no common month field. If month field is there it will more easy but as see your excel data you need to load a cross table and make a common field and from another table create a MonthYear field for calculation.

See the attached load example and load your table according to that.

Tab1:

CrossTable(MONTHS, Data, 2)

LOAD YEAR,

MONTH,

JAN,

FEB,

MAR,

APR,

MAY,

JUN,

JUL,

AUG,

SEP,

OCT,

NOV,

DEC

FROM

(biff, embedded labels, table is Sheet1$);

Data:

LOAD *,

if(MONTHS='JAN',1,

if(MONTHS='FEB',2,

if(MONTHS='MAR',3,

if(MONTHS='APR',4,

if(MONTHS='MAY',5,

if(MONTHS='JUN',6,

if(MONTHS='JUL',7,

if(MONTHS='AUG',8,

if(MONTHS='SEP',9,

if(MONTHS='OCT',10,

if(MONTHS='NOV',11,

if(MONTHS='DEC',12)))))))))))) AS NumMonth,

MonthName(MakeDate(YEAR,MONTH)) as MonthYear,

Month(MakeDate(YEAR,MONTH)) as NewMonth

Resident Tab1;

DROP Table Tab1;

EXIT Script;

Change excel load as your excel location in your system

And after loading of table take a Pivot table

Dimension1 :- YEAR

Dimension2:- NumMonth

Dimension3:- NewMonth

Expression:- SUM({<YEAR=,MonthYear = {'>=$(=Max(MonthYear))'}>} Data)

See the attached snapshot

Re: Month Order

preminqlik — Sun, 13 Apr 2014 14:52:34 GMT

Hi ,

Find attachment