Year Month

[upaliwije]

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

[rajat2392]

here is the solution for this problem..see the attached files....

Pls don't forget to mark it as correct if u are satisfied with the solution....

[MarcoWedel]

@QlikCommunity moderator:

why is my first answer to this thread still in moderation status and thus invisible to the OP?

[MarcoWedel]

@QlikCommunity moderator:

I still would appreciate an explanation as to why my post triggered the moderation function ...

[upaliwije]

Hi Rajat

Thanks a lot for your help

sum({1}$(=Upper(Month(AddMonths(MakeDate(SubField(Year_Month,'_',1), SubField(Year_Month,'_',2)),0)))))

Now I want to modify the above expression to show data relating to selection made. Eg If I select Year as 2013 only the following records should be shown. Pls help me

YEAR	MONTH	JAN_NRP	FEB_NRP	MAR_NRP	APR_NRP	MAY_NRP	JUN_NRP	JUL_NRP	AUG_NRP	SEP_NRP	OCT_NRP	NOV_NRP	DEC_NRP
2013	1	4122270.14	5139519.86	6129330.21	4060620.24	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	2	4122270.14	5225041.15	6129330.21	4060620.24	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	3	4122270.14	5225041.15	5436199.16	4060620.24	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	4	4122270.14	5225041.15	5436199.16	3911852.94	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	5	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	6	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	7	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	8	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	4492296.77	5708382.43	5472064.98	5573470.37
2013	9	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	5708382.43	5472064.98	5573470.37
2013	10	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	4616778.98	5472064.98	5573470.37
2013	11	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	4616778.98	4843327.16	5573470.37
2013	12	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	4616778.98	4843327.16	3715367.73

[rajat2392]

Hi Upali,

check the attached app....

[upaliwije]

Dear Rajat

Thanks a  lot . It is working Fine

[rajat2392]

welcome Upali..can you please mark it as correct...

[upaliwije]

Dear Rajat

Thanks a lot for your help.

I have a small issue and need your help to solve.

I want to create a pivot table with a single row which should show the data highlighted in bold letteers by me in a single Row. All other conditions applied to previous pivot table should remain unchanged.

YEAR	MONTH	JAN_NRP	FEB_NRP	MAR_NRP	APR_NRP	MAY_NRP	JUN_NRP	JUL_NRP	AUG_NRP	SEP_NRP	OCT_NRP	NOV_NRP	DEC_NRP
2013	1	4,122,270	5,139,520	6,129,330	4,060,620	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	2	4,122,270	5,225,041	6,129,330	4,060,620	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	3	4,122,270	5,225,041	5,436,199	4,060,620	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	4	4,122,270	5,225,041	5,436,199	3,911,853	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	5	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	6	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	7	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	8	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	4,492,297	5,708,382	5,472,065	5,573,470
2013	9	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	5,708,382	5,472,065	5,573,470
2013	10	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	5,472,065	5,573,470
2013	11	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	5,573,470
2013	12	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	3,715,368