Rolling 6 weeks

[rustyotters]

Hello

I have a query in BI4 where I return all 52 weeks into a report. In the report I only wish to show the last "rolling" 6 weeks worth of data week on week.

I have tried ranking the dates but this doesn't work.

Due to the number of queries I have in the report, I don't want to create another query as I do need to also show in another report YTD at the end of the last week.

Would appreciate your assistance.

 

[BobSuruncle]

Here's a very simplistic way to get what you're after.
It does not take into account whether you want the full week, or whether the week starts on Sunday or Monday, or whether you want only those values on Business Days, as you have not specified any of this.

Insert this formula into the Details section and create a Summary (i.e. Sum) on this formula.

// Store the date of the transaction to a DateTime variable 'd'
Local DateTimeVar d := {SampleOrders.OrderDate} ;

// Store the current DateTime to a DateTime variable 'c'
Local DateTimeVar c := DateTime(PrintDate, PrintTime) ;

// Subtract 6 weeks from the Current DateTime and store this to a DateTime variable 'w6'
Local DateTimeVar w6 := DateAdd ('ww',-6 , c) ;

//Check to see if the current record is within the last 6 weeks
If d in w6 to c then {SampleOrders.Amount} else 0

Bob Suruncle