I need to create the below table showing '% on track' which is displayed against countries. The Year-Month showed on each column are from Calendar dimension. I have used Calendar table as a data Island because I have more then 40 dates present in my datawarehouse. Now I have a problem creating YTD.
YTD is calcuated only for current year irrespective of calendar Month Year selection. It means that if user select Year as 2011 and month as June to Dec, the YTD still should be calculated for Jan-12 to Jun-12(current month).
|% On Track||12-Jan||12-Feb||12-Mar||12-Apr||12-May||12-Jun||12-Jul||12-Aug||YTD|
Also the Summary row is showing the sub-total of all the percentages which is wrong and should be calculated across the countries for each month.
I have attached the qvw and related dummy data explaining my problem.
Please suggest me some solution.
Hope I am able to explain my problem.