3 Replies Latest reply: Dec 28, 2016 6:53 AM by Kristof Van den Bossche RSS

    Specific Months and YTD as Dimension in a Horizontal Straight Table

    Kristof Van den Bossche

      I am currently struggling on a dimension in a horizontal straight table and I can't seem to find a solution myself or in existing Discussions.

       

      What I want is to show 5 columns:

      • OK Current Month - 2
      • OK Current Month - 1
      • OK Current Month
      • OK Same Month as Current from Y-1
      • NOK YTD

       

      The first 4 are OK but I can't find a way to insert the YTD as column n° 5, the difficulty is of course that YTD is a calculation of more than one month. Is there any way I can insert it on the same 'level' as the months (e.g.: 2016 - YTD)?

      MonthsAndYTD.PNG

      As you can see I have 2 dimensions (I can't use Tab Expressions for DateTime because I need it to calculate a set of complex KPI's there):

       

      • Dimension 1 Year: DateCalendar.Year, this is just a field

       

      • Dimension 2 FinRep lay-out: =if(DateCalendar.Month >= month(today())-2 and DateCalendar.Month <= month(today()) and DateCalendar.Year = year(today())or DateCalendar.Year = year(today())-1 and DateCalendar.Month = month(today()),DateCalendar.Month)

       

      Any ideas how I can get the YTD 2016 inside this Dimension Expression?

       

      PS: I realise I will be stuck in January with the Month - 2 part but I will fix this later .

       

      Thanks in advance.