Sales Last Year Same Month Day

[madnanansari]

I am working Sales table have 3 years data with the below attributes:

[Date]

[Sales Amount]

The requirement is to show a TABLE where for each [Date], Sales for Last year same Month day should also come.

For example: Against the date 10-Dec-2017, the last year same Month day sales also come which is sales for 11-Dec-2016. (Sunday this month same as sunday last year same Month which is 11th december).

If I subtract 364 from [Date]; it will give me Last Year Same day.

[antonaks]

Hi!

Try to use makedate()

makedate(current_year-1, month_of_current_year, day_of_current_year) as ....

[madnanansari]

what to do next then?

[madnanansari]

How to use set analysis to bring the sales for last year same month day?

[antonaks]

Can you show how must final result seen? (Table or Diagram) May be I don't understand your question.

[sunny_talwar]

May be use this approach

The As-Of Table

[madnanansari]

Date                     Sales      Sales LYSD

02-Dec-2016       101        0

03-Dec-2016       103        0

…..

…..

01-DEC-2017      55           101

02-Dec-2017       100          103

[Anonymous]

Note :

you have to get  "Year" and "WEEK",   out of your "Date" field

Current/selected year

Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} Sales)

Prior year (to same week number as current/selected year):

  Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year)-1)}>} Sales)

[antonaks]

temp_data:

LOAD Date,

     Year(Date) as Year,

     Month(Date) as Month,

     Day(Date) as Day,

     num(Month(Date)) & '-' & Day(Date) as Month_Day,

     Sales

FROM

(ooxml, embedded labels, table is Лист1);

[antonaks]

Try this.