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



      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?







        DECLARE FIELD DEFINITION Tagged ('$date')


        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] ;