Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi!
Try to use makedate()
makedate(current_year-1, month_of_current_year, day_of_current_year) as ....
what to do next then?
How to use set analysis to bring the sales for last year same month day?
Can you show how must final result seen? (Table or Diagram) May be I don't understand your question.
May be use this approach
Date Sales Sales LYSD
02-Dec-2016 101 0
03-Dec-2016 103 0
…..
…..
01-DEC-2017 55 101
02-Dec-2017 100 103
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)
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);
Try this.