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?

 Section Owner 10/1-6/13/2015 10/1-6/13/2016 Difference % % of Total Current YTD Section1 Owner1 1000 1200 20% 3% Section2 Owner2 2000 2800 40% 3% Section3 Owner3 3000 4400 47% 6% Section4 Owner4 4000 6000 50% 8% Section5 Owner5 5000 7600 52% 11% Section6 Owner6 6000 9200 53% 15% Section7 Owner7 7000 2400 -66% 5% Section8 Owner8 8000 2600 -68% 5% Section9 Owner9 9000 2800 -69% 6% Section10 Owner10 10000 3000 -70% 7% 55000 42000 -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)

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.