0 Replies Latest reply: May 10, 2016 7:44 AM by Jean-Marc Nihoul RSS

    How to calculate relative time with autoCalendar

    Jean-Marc Nihoul

      Hello,

       

      I have created an application in which I have used autoCalendar to add some time related attributes (see below). This is autogenerated and pretty useful. Now I need to add relative time flags, e.a. YTD, previous YTD, MTD, rolling 12 months, current day, current month,... I want all these flags to be calculated based on the maximum date found in the fact table. So if in my fact table I have data until the 23th of november 2015, I want this date to be the current date and all other relative time flags should be derived from this date. Can someone help me out here?

       

      Cheers,

       

      JM

       

      [autoCalendar]:

        DECLARE FIELD DEFINITION Tagged ('$date')

      FIELDS

        Dual(Year($1), YearStart($1)) AS [Jaar] Tagged ('$axis', '$year'),

        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Kwartaal] Tagged ('$quarter'),

        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [Jaar kwartaal] Tagged ('$axis', '$yearquarter'),

        Month($1) AS [Maand] Tagged ('$month'),

        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [Jaar maand] Tagged ('$axis', '$yearmonth'),

        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

        Date(Floor($1)) AS [Datum] Tagged ('$date');

      DERIVE FIELDS FROM FIELDS [Bezoek - ontslagdatum], [Bezoek - opnamedatum] USING [autoCalendar] ;