1 Reply Latest reply: Oct 29, 2012 11:35 AM by Stefan Wühl RSS

    adding specific date to the report

      Hi,

      do you know if it is possible to create a field with date if i am not using any dates loading?

       

      This is my data:

      report     refreshment frequency          timing

      1               weekly                            Mondays

      2               monthly                           first Fridays of month

       

      I would like to get the following output:

      report     refreshment frequency          timing                              next refreshment date

      1               weekly                            Mondays                              05.11.2012 (next Monday)

      2               monthly                           first Fridays of month             02.11.2012 (first Friday in coming month - Nov)

       

      Many thanks for help.

      J.

        • Re: adding specific date to the report
          Stefan Wühl

          Should be possible in principle.

           

          You would need to define all timings and parse the field appropriately. For example, you can calculate the next Monday as of today like

           

          =Date(today() + (7-WeekDay(today()) ) )

           

           

          Then your script could look like

           

          LOAD

          report, [refreshment frequency], timing,

          if( timing = 'Mondays', Date(today() + (7-WeekDay(today()) )), 'other calculations') as [next refreshment date]

          FROM ...

           

          but there are probably better / more elegant ways to pick the correct expression.

           

          Hope this helps,

          Stefan