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
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
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
Hi ,
Find attachment