Current plus previous 13 weeks

[richnorris]

Hi,

So, I'm trying to create a chart that shows the current week plus previous 13 weeks. The dashboard contains more years than just the current year, but thats cool, I want to show data that is in those weeks for previous years. I'm trying to use set analysis, but its not working quite right. Anyone know whats wrong with the below? Its showing 15 weeks for some reason.

Sum({<Week ={">$(=num(Week(Today())-13))"}, Week ={"<=$(=num(Week(Today())))"}>} [$ VA])

[its_anandrjs]

What about some thing like

='W'&Sum({<Week ={">$(=num(Week(Today())-13))"} >} [$ VA])

[jonasheisterkam]

I would use a nother expression, week gives only the week in the year. Use st like Datefiled >= floor(weekstart(today-13*7))

[richnorris]

What do you mean when you say it gives 'only the week in the year' ? If I select week 3, but don't select a year, it will select the third week of all four years that I have in the dashboard. This is what I want.

That said, I think you're onto something with just using days * 7, I think thats probably cray cray enough to work.

[richnorris · Accepted solution]

Sorry, was just being a nump-tron, it was applying the two set analysis' sequentially, with the second overwriting the first, so it was actually just doing Week <= Current, hence the extra weeks. what I actually needed was just to combine them into one big ol' statement like thus:

Sum({<Week ={">$(=num(Week(Today())-13))<=$(=num(Week(Today())))"}>} [$ VA])

Cheers for all your help though chaps, Jonas I reckon in the future I'll just do what you said, rather than messing around with the various different date-types. Seriously, why is there an AddMonths() but no AddDays / AddWeeks etc? So many caveats like that to deal with, better off just breaking everything down into days and then doing it the manly / manual way.

[nagaiank]

You may add a field WeekID in your calendar which has week number as sequence numbers.

Choose your dimensions as

If(WeekID >= (current WeekID -13), WeekID,Null())

You can store current WeekID in a variable or calculate using Max(WeekID)

Hope this helps.