Year end Week 52 alternative method?

[devarasu07]

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

[rubenmarin]

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

[Digvijay_Singh]

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.

[sunny_talwar]

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

[rahulpawarb]

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

[Colin-Albert]

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.