topic Re: Same day, same week, previous year Calc in QlikView

Same day, same week, previous year Calc

PK007 — Sat, 16 Nov 2024 00:06:25 GMT

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.

Re: Same day, same week, previous year Calc

Vegar — Fri, 21 Aug 2020 10:35:55 GMT

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/DateAndTimeFunctions/makeweekdate.htm

Try something like this.

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

Re: Same day, same week, previous year Calc

PK007 — Tue, 25 Aug 2020 10:59:00 GMT

Hi @Vegar , thank you for your reply.

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

Re: Same day, same week, previous year Calc

tresB — Tue, 25 Aug 2020 11:15:19 GMT

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

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

Re: Same day, same week, previous year Calc

PK007 — Tue, 25 Aug 2020 13:33:32 GMT

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

Re: Same day, same week, previous year Calc

Vegar — Wed, 26 Aug 2020 08:58:44 GMT

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

Re: Same day, same week, previous year Calc

Vegar — Wed, 26 Aug 2020 09:06:52 GMT

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)

Re: Same day, same week, previous year Calc

PK007 — Thu, 27 Aug 2020 12:10:31 GMT

Thanks @Vegar , that works perfectly!