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.
I need sales for the day which is 364 days back. I don't need the sale for same month same day number.
If let say today is 27th Dec 2017 and wednesday then I need the sales for wednesday the 28 Dec 2016 which is exactly 364 days back. I cannot compare Wednesday (27 dec 2017) with tuesdays (27 dec 2016) ; that's not correct.
Ok. Then you can create complex key with week number and day number.
But... I correctly understood that in the case of January 2016 and January 2017, you will compare 01/01/2017 and 03/01/2016, because this is one day of the week?
I apologize, at first the date was written incorrectly. Already fixed.
Any guidance?
LOAD
Date,
Week(Date) & '-' & num(WeekDay(Date)) as WeekNum_WeekDay, // Key that you need
Sales
FROM
Is the As-Of-Table approach not working? Have you tried it already?
[AsOf Master Calendar]:
Load
[Date],
Date([Date] - 364,'D/M/YYYY') as [Date LYSMSD],
-364 as [Date LYSMSD Diff]
Resident [Master Calendar];
I am using the standard master calendar.
Now when I try: sum({$<[Date]= {"$(=Max([Date LYSMSD]))"}>} [Sales]), its not bringing the results.
Try this
AsOfTable:
LOAD Date as AsOfDate,
Date,
'CY' as Flag
Resident [Master Calendar];
Concatenate (AsOfTable)
LOAD Date as AsOfDate,
Date([Date] - 364,'D/M/YYYY') as Date,
'PY' as Flag
Resident [Master Calendar];
Now you need to use AsOfDate in your chart instead of using Date as a dimension and this expression
Sum({<Flag = {'PY'}>}Sales)