Comparing Year over Year using days not Dates

Hi Tiana,

Thanks for reaching out. Assuming you're using filtering to obtain this YOY sales data, the simplest way to achieve this would likely be to use a date filter for your data, setting the operand to "Between", going into the filter Format section and under Entry Style, setting the Default Value to Dynamic Date > - 368 Days and Today's Date:

It's -368 scripting-wise because it's including the past 365 days as well as the chosen day (10/17/18, the 367th day 10/17/17 and the 368th day 10/16/18... in case you were wondering). Of course if you don't need the actual data of the 17th of 2018 you can edit the above as per your requirements.

This may also be possible via scripting a Freehand SQL statement in a Calculated Field, or by creating a Custom Function, though the exact scripting of which is not something we can assist with. The filtering option though should give you the results you're looking for, and rather quickly and be useful in most circumstances.

Hopefully this helps! Please let me know if this seems to be what you were looking for and whether you have any questions.

Regards,

Mike

Hi Mike,

I am using a subquery report where the main query is filtered by Calendar year to date and the append query is filtered by last calendar year. The queries are based off of "month = month" and "day of month = day of month". I'm not sure if it is feasible to do so in this way/format.

Regards,

Tiana

Hi Tiana,

I haven't forgotten about this case. I know we've had a decent amount of back-and-forth in a couple other tickets since, hah. Since it's not really Support's domain to build reports based on specific use cases (our primary domain is replicating defective behavior), I'm not quite sure how to accomplish this based on your setup.

I've reached out to some of my colleagues to see if they have some idea on this one. I suspect, and am hoping for, confirmation that what you're trying to accomplish is either A) a relatively quick and straight forward solution that I'm just not seeing at this time; or B) not possible to do in this manner. Either way, I'll let you know as soon as I find out.

Regards,

Mike

Hi Tiana,

I just wanted to check in and see whether this is still required on your end. I'd think this could be accomplished by drafting a Custom Function that references Today - 364 days (the exact scripting of which would be dependent on your RDBMS), as 364 days ago from today is a Monday, that would then be found in your Calculated Field's filter list.

Something along the lines of:

SELECT * FROM dbo.trsmecp WHERE Time_Listened 
BETWEEN DATEADD(day, -364, GETDATE()) AND GETDATE();

For your reference, I pieced this together based on a thread I searched for and found here.

Regards,

Mike

Hi Tiana,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you on this, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike