Show Today's Sales and Same Day Last Year's Sales

[asmithids]

Hello,

I am creating a straight table to show the current day's sales amount and the same date last year's sales amount with variance by store (see attached screen shot of table layout and data model).   I have found postings on comparing sales amounts between the current date and the same day last year, but the suggested solutions are not applicable to what I am trying to solve.  I am having difficulty putting the current date and the current date sales amount with the same day last year's sales amount on the same row by Store. 

I need to just show the current day's sales (i.e. Max(date) function) and the same date last year's sales .  For example, sales for all stores for today 1/9/2016 and 1/9/2015 with variance.  Each day the dashboard needs to only show that day's sales with prior date sales. 

I believe the right approach is to use set analysis expressions, but I'm not sure of the specific syntax to get the table to show the data. 

Any help on the coding is always highly appreciated! 

Thank you,

[Gabriel]

Hi,

My advice is if you have Calendar script created, if not use Richard Pearce Calendar Calendar with AsOf Flags, Compare Easter to Easter and create %Flag_SameDayYear_M01 flag as below.

If(Possible_KeyDate=addmonths(%KeyDate,-12),1,0)

As %Flag_SameDayYear_M01

[asmithids]

Hi Gabriel,

I tested Richard Pearce's Calendar.  It generated over 4.4 million records based on a fact table with just over 2,000 records.  To maintain performance as the production data set grows, I think using set analysis expressions to render the same day prior year sales would be more efficient in the long run. 

I think Richard's calendar is WELL designed and I do see how I can leverage it on future projects. 

Thank you for the help.

[settu_periasamy · Accepted solution]

Hi Alec,

I think the problem with the date format..the below expression working fine..

=Sum({$< Date={"$(=Date(Max(Date),'MM/DD/YYYY'))"}>} [Net Sales])

Check the Attachment..

[HirisH_V7]

Hi,

This is for today,

=SUM({<Year={'$(max(Year))'},Date={'<=$(max(Date))'},Month=,Day=,MonthYear=>}Amount)

This is for same day previous year,

=SUM({<Year={'$(max(Year)-1)'},Month=,Day=,Date={'<=$(date(addyears(max(Date), -1), 'DD MMM YYYY')'},MonthYear=>}Amount)

Hope this helps,

Regards

[sunny_talwar]

Beautifully Done. Thanks Settu

[settu_periasamy]

No problem sunindia‌

I used your suggestions Above ()  ☺

[asmithids]

That worked!!!  Thank you settu!!!

Thank you to EVERYONE for helping me with this issue!!!

[asmithids]

Hi Sunny,

Thank you for the overall solution.  I really appreciate your help.