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

    Header Name as (Start Date - Today)

    srinivas.anupoju.0

      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
      Section1Owner11000120020%3%
      Section2Owner22000280040%3%
      Section3Owner33000440047%6%
      Section4Owner44000600050%8%
      Section5Owner55000760052%11%
      Section6Owner66000920053%15%
      Section7Owner770002400-66%5%
      Section8Owner880002600-68%5%
      Section9Owner990002800-69%6%
      Section10Owner10100003000-70%7%
      5500042000-24%

       

      TIA

       

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

       

      Warm Regards,

      Srinivas

        • 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.