Same day, same week, previous year Calc

[PK007]

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.

[Vegar]

You should be able to do this using the makeweekdate() in some way. 

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

Try something like this. 

Sum({<Date={'$(=MakeWeekDate(year(Max(Date)-1), week(max(Date)), weekday(max(Date)) )' }> } TotalSales)

[PK007]

Hi @Vegar , thank you for your reply.

I have added the script you have provided but it brings back "-".

[tresesco]

Year reference needs to be corrected I guess. Try like:

Sum({<Date={'$(=MakeWeekDate(year(Max(Date))-1, week(max(Date)), weekday(max(Date)) )' }> } TotalSales)

[PK007]

Thanks @tresesco , I unfortunitely still get the result as above '-'.

[Vegar]

Is your [Date] field formatted in the same format as your  app DateFormat variable (Your default date format)?

[Vegar · Accepted solution]

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)

[PK007]

Thanks @Vegar  , that works perfectly!