0 Replies Latest reply: Jun 28, 2012 3:50 AM by Anosh Nathaniel RSS

    Need to calculate YTD in below format

    Anosh Nathaniel

      Hi All,

       

      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 Track12-Jan12-Feb12-Mar12-Apr12-May12-Jun12-Jul12-AugYTD
      India10%11%12%13%14%34%35%43%45%
      USA65%11%12%23%14%23%1%43%41%
      UK11%34%12%13%13%34%35%43%99%
      Summary62%11%12%25%12%23%51%42%49%

       

      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.

      Anosh Nathaniel