Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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....
@QlikCommunity moderator:
why is my first answer to this thread still in moderation status and thus invisible to the OP?
@QlikCommunity moderator:
I still would appreciate an explanation as to why my post triggered the moderation function ...
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 |
Hi Upali,
check the attached app....
Dear Rajat
Thanks a lot . It is working Fine
welcome Upali..can you please mark it as correct...
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 |