Variable to show last 13 weeks and next 13 weeks

[Dayna]

Hello,

I am trying to make a chart show data from the last 13 weeks and next 13 weeks from todays date.

I was thinking of a variable to get the data, and then use subfields to extract it... But I think i've gone about it the wrong way!

What i've done so far is:

=(

Year(Today())&'.'&Week(Today()))-0.13&

','&(Year(Today())&'.'&Week(Today())-0.12)&

','&(Year(Today())&'.'&Week(Today())-0.11)&

','&(Year(Today())&'.'&Week(Today())-0.10)&

','&(Year(Today())&'.'&Week(Today())-0.09)&

','&(Year(Today())&'.'&Week(Today())-0.08)&

','&(Year(Today())&'.'&Week(Today())-0.07)&

','&(Year(Today())&'.'&Week(Today())-0.06)&

','&(Year(Today())&'.'&Week(Today())-0.05)&

','&(Year(Today())&'.'&Week(Today())-0.04)&

','&(Year(Today())&'.'&Week(Today())-0.03)&

','&(Year(Today())&'.'&Week(Today())-0.02)&

','&(Year(Today())&'.'&Week(Today())-0.01)&

','&(Year(Today())&'.'&Week(Today()))

And the same again for next 13 weeks, but not sure... Could someone help me out? As Im getting confused what is the best practice for doing this 🙂

Many thanks in advance!!

Kind Regards,

Dayna

[#2]

can you explain a bit, like, what you are trying to achieve. ie, you are plotting graph on What Vs What?

[Dayna]

Yes of course, I am trying to use a bar chart to show the actual sales and forecasted sales for parts (data taken out of an array) for the last 13 weeks and next 13 weeks.

I need this data to change on a weekly basis so it is always showing last 13 and next 13 weeks regardless. This needs to cover year changes from 2009 to 2010 (However, I have been using a field which is just Year.Week i.e. 2009.31).

[#4]

just check it out

[Dayna]

That's pretty damn good!! Will it work on Years tho?

I've applied it to mine, the last 13 half works, but the next 13 realyl doesn't.. And im not sure as to why!

I've attached it if you could have a look?

Kind Regards,

Dayna

[#6 · Accepted solution]

You need to modify your expression. Check the attached file.

[Dayna]

That is brilliant, thank you so much for your help!!

Kind Regards,

Dayna

[johanlind]

Is it possible to use this function for displaying by months.

Last 3 months and next 3 months.

[Dayna]

Hello,

If you change the number in the expression, it will show the according value:

if

(weekstart(today())-makeweekdate(FCS_Year,FCS_Week)<=91 AND weekstart(today())-makeweekdate(FCS_Year,FCS_Week)>=0, sum(FCS_Qty))

Is for the last 13 weeks but in days - just change this value to what you require.

[johanlind]

I have tried that solution but it only shows results by week not by months.