1 Reply Latest reply on Jun 16, 2016 2:30 PM by Saurabh Singhal

    Header Name as (Start Date - Today)


      Hi All,


      How to show a header name as "Start Date - Today"


      1. Performance year starts from Oct 1 and ends on Sep 30. Current performance year 2016 is 01 Oct 2015 - 30 Sep 2016.
      2. Header Name should show as 10/1 - 6/13/2016 that is start date of the performance year to the till date(current day) - below is the prototype with sample data
      3. I am comparing two performance years
      4. Difference % is ((10/1-6/13/2016)-(10/1-6/13/2015))/((10/1-6/13/2015))
      5. % of Total Current YTD is 1200/42000 and so on


      I am in need of the calculated fields for above specified

      The actual data consists null values in the columns 10/1-6/13/2016, 10/1-6/13/2015. How do I over come them? This is the key catch for any error values for the end users / business users

      How should I build the calculated fields for my above requirements, do i need 4 of them or can I do it in a best possible way based on your thoughts/ideas?



      SectionOwner10/1-6/13/201510/1-6/13/2016Difference %% of Total Current YTD




      Please let me know if am unclear in specifying what I am looking for.


      Warm Regards,


        • 1. Re: Header Name as (Start Date - Today)
          Saurabh Singhal

          Hi Srinivas,


          Based on my understanding of what I understood from here, you are looking for field calculations to arrive at CY & LY sales. If that is correct then for your scenario, below calculation should be helpful:

          CY: SUM(IF ([Date] <= TODAY()) AND ([Date] > MAKEDATE(year(TODAY())-1,10,1)) then [Sales] else 0 END)

          LY: SUM(IF ([Date] < MAKEDATE(year(TODAY())-1,10,1)) AND ([Date] > MAKEDATE(year(TODAY())-2,10,1)) then [Sales] else 0 END)


          For CY calculation you may as use {Max([Date])} in case DB is not updated on a daily basis. Also, the values of 10 & 1 in MakeDate function made dynamic, just in case FY definition changes later on.


          Once these two calculations are on the shelf, simple table calculation (Previous, Difference) should help achieve difference & % Change.