topic Year end Week 52 alternative method? in QlikView

Year end Week 52 alternative method?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I've weekly report like below, when user select Year as 2017 and week as 1 then my report having issue for showing last week as 0 so i just to like below expression. is there any alternative method to make easier?

column header for last week

='Qty Sold WK ' & if($(vLastWeek)=0,52,$(vLastWeek))

Last Week Sold Qty expression,

If($(vLastWeek)=0, sum({<Week={'52'}>} SoldQty), sum({<Week={'$(vLastWeek)'}>} SoldQty))

created variable,

vLastWeek=Max(Week(Date))-1

Re: Year end Week 52 alternative method?

rubenmarin — Tue, 17 Jan 2017 22:57:51 GMT

Hi Devarasu, If week is generated with Week(Date) and you want the last week you can try with:

vLastWeek=Week(Max(Date)-7)

Also, that gives the correct result for week 52? it's supposed to be week 52 of last year, didn't? Maybe you need a year-week field

Re: Year end Week 52 alternative method?

Digvijay_Singh — Tue, 17 Jan 2017 23:00:29 GMT

If you are trying to get value for last week of 2016, you may need to change year as well by Max(Year)-1 in your set expression, currently it might be showing value for year 2017 and week 52, which is future date and may be showing 0 due to that.

Re: Year end Week 52 alternative method?

sunny_talwar — Wed, 18 Jan 2017 02:49:35 GMT

I suggest using Dates instead of just weeks to achieve this kind of requirement

Re: Year end Week 52 alternative method?

rahulpawarb — Wed, 18 Jan 2017 05:46:15 GMT

Hello Devarasu,

I am sure that you are doing well!

I believe your data is at week's granularity. I have further modified the definition of vLastWeek variable to retrieve expected week no. Please use & validate below given updated definitions.

Updated Variable Definition:

LET vLastWeek = If(Week(Date) = 1, Week(Date - (Day(Date) + 1)), Week(Date)-1) ;

Updated column header for last week:

='Qty Sold WK ' & $(vLastWeek)

Updated Last Week Sold Qty expression:

Sum({<Week={'$(vLastWeek)'}>} SoldQty)

Hope this will be helpful.

Regards!

Rahul

Re: Year end Week 52 alternative method?

Colin-Albert — Wed, 18 Jan 2017 08:03:56 GMT

It may be worth adding a relative week field to your data model, so the current week is week 0, last week is week1, etc.

This blog gives an overview of the technique. Relative Calendar Fields

You may need to combine the week() function and weekyear(), as the year when dealing with weeks at the start and end of the year can differ from the calendar year. Or use the weekname() function.