4 Replies Latest reply on Oct 18, 2016 2:32 AM by chris.sutcliffe

# Counts YoY (current day over same day last year)

Hi everyone,

I'm having a really tough time trying to figure out how to show the below data with the final intention of creating a bar graph with a YoY overlay line.

So for example:

 sales date Total Same DAY Last Year Total Same DAY last Year 2016-10-18 1020 2015-10-20 990

So I basically want to:

1. count the total based on the current date

2. count the total based on the current dates same day last year.

the sales date is in the data and the same day last year is created using a calculated field but I can't for the life of me figure out how to produce the counts.

If anyone can point me in the right direction that would be awesome.

Chris

• ###### 1. Re: Counts YoY (current day over same day last year)

Hi Chris

try to this tread as they mentioned solution for it with sample data. I hope it helps

• ###### 2. Re: Counts YoY (current day over same day last year)

Hi there,

Thanks for the link, I have already attempted going these solutions to help with my problem but none seem to fit the exact problem I have. If anyone has any other suggestions I would really appreciate it!

The logic I have in my head to solve this is something like this:

IF Sales Date = 2016-10-18 THEN SUM Sales WHERE Sales Date = 2015-10-20.

Basically I want to take the sales date and only count the sales from the same day in the previous year. I have the date calculation down just don't know how to link the counts.

Chris

• ###### 3. Re: Counts YoY (current day over same day last year)

Hi Chris,

Please try the below formula which might help

Sales today and same day last year:

if DATEDIFF('day',[Order Date],TODAY())=0 or

DATEDIFF('day',[Order Date],TODAY())=366 then [Sales]

end

If you want to make the dates static you can write something like below

if [Order Date]= #18-10-2016#  or [Order Date]= #20-10-2015# then [Sales]

end

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

you can use parameter in the place of dates and make it dynamic

if [Order Date]= #Date parameter1#  or [Order Date]= #Date parameter2# then [Sales]

end

Thanks

1 of 1 people found this helpful
• ###### 4. Re: Counts YoY (current day over same day last year)

Hi Praveen,

Thanks, i'm not sure if I'm implementing this correctly. It seems to just give me two values and not on the same line, for example:

 sales date Same DAY last year Sales 2015-10-20 2014-10-20 990 .... ... 2016-10-16 2015-10-18 2016-10-17 2015-10-19 2016-10-18 2015-10-20 1020

It might be because I'm forced to wrap the DATEDIFF in an ATTR function so its stops complaining about mixing aggregate and non aggregates (my sales is another calculated field using COUNTD)

Which is ALMOST there, I just need to be able to do it for all dates and be on the same line, for example

 sales date same day last year Sales sales same day last year 2016-10-18 2015-10-20 1020 990

Once I have these nailed down I can then calculate my YoY growth

Thanks again for the help.