Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone. I have a problem trying to add a MTD and YTD figures in a pivot table, please refer as below screen dump which what i expected, the YTD figure will be as of Mar 2014, and the Total figure will be as of Dec 2014, i have create a pivot table as attached, every things are OK except the YTD figure , I don't know how to add the YTD column in the pivot table, could you plesae help? Thanks a lot.
HI WU Paul,
Solution is to add a total in the script.
Step 1:
create a Month field which also contains YTD as month name:
Months:
/* Create a custom month variable to add the Ytd */
load dual ( string,numrep ) as MonthName inline [
string,numrep
Jan , 1
Feb , 2
Mar , 3
Apr , 4
May , 5
Jun , 6
Jul , 7
Aug , 8
Sep , 9
Oct , 10
Nov , 11
Dec , 12
YtD , 13
];
Step 2
Add the YtD totals in the script:
Concatenate
Sales:
Load
Sales,
Date(Max(SalesDate)) as SalesDate,
Year as year2,
13 as MonthName, /* 13=YtD */
sum(Target) as Target,
sum(Amount) as Amount
resident Sales
where SalesDate < '$(vCurrentMonth)'
group by Sales,Year;
Step 3:
Because the YtD is not a real month, you to adjust your pivot so that the newly created year and MontName fields are used.
See attached solution