Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have looked at the questions similar to mine but non of them works for me.
I currently have Sales table which shows latest Sales figures (always yesterdays):
=Sum({<Date={">=$(=Max(num(Date)))<=$(=Max(num(Date)))"}> } TotalSales)
And then same date last year:
if(weekday(addyears(max(Date),-1)) = 'Sun'
, Sum({<Date={">=$(=addyears(Max(num(Date))-1,-1))<=$(=addyears(Max(num(Date))-1,-1))"}>} TotalSales)
, Sum({<Date={">=$(=addyears(Max(num(Date)),-1))<=$(=addyears(Max(num(Date)),-1))"}>} TotalSales)
)
I need a column which gives me the Sales for the same day, same week, previous year. For instance : Friday 14/08/2020 compare to Friday 16/08/2019.
Is there an easy way to get the result?
Thanks in advance.
Oops I noticed that I had a couple of paranthesis misstake in my expression. (I originally typed it on my mobile device)
This year week value:
=Sum({<Date={'$(=MakeWeekDate(year(Max(Date)), week(max(Date)), weekday(max(Date))))' }> } TotalSales)
Previous year week value:
=Sum({<Date={'$(=MakeWeekDate(year(Max(Date))-1, week(max(Date)), weekday(max(Date))))' }> } TotalSales)
You should be able to do this using the makeweekdate() in some way.
Try something like this.
Sum({<Date={'$(=MakeWeekDate(year(Max(Date)-1), week(max(Date)), weekday(max(Date)) )' }> } TotalSales)
Hi @Vegar , thank you for your reply.
I have added the script you have provided but it brings back "-".
Year reference needs to be corrected I guess. Try like:
Sum({<Date={'$(=MakeWeekDate(year(Max(Date))-1, week(max(Date)), weekday(max(Date)) )' }> } TotalSales)
Thanks @tresesco , I unfortunitely still get the result as above '-'.
Is your [Date] field formatted in the same format as your app DateFormat variable (Your default date format)?
Oops I noticed that I had a couple of paranthesis misstake in my expression. (I originally typed it on my mobile device)
This year week value:
=Sum({<Date={'$(=MakeWeekDate(year(Max(Date)), week(max(Date)), weekday(max(Date))))' }> } TotalSales)
Previous year week value:
=Sum({<Date={'$(=MakeWeekDate(year(Max(Date))-1, week(max(Date)), weekday(max(Date))))' }> } TotalSales)
Thanks @Vegar , that works perfectly!