Need to dynamically filter a six week range, based on selected week and year

[mikegrattan]

Let's say I have the following fields in a table:

• ShipDate
• Week
• Year
• QuantityShipped
• Six Week Average

If a person selects a week and a year, I want to dynamically calculate the Six Week Average of Quantity Shipped. Keeping in mind that a person could choose some combination of Week and Year that would overlap to another year, perhaps the best approach would be something like this:

1. Find the WeekStart day of Week and Year to get the ending date of the Six Week period.
2. Count backwards six weeks from the WeekStart date to get the beginning of the Six Week period.

Now...how to put that in an expression so it can be done dynamically?

I started by trying this equation just to get the WeekStart of the ShipDate, but it returns records for all years and weeks, even though I specified variable values of '05' for the Week, and '2025' for the year:

=WeekStart({<Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}ShipDate)

Any ideas or suggestions would be greatly appreciated!

[Bhushan_Mahajan]

@mikegrattan Try below expression:

WeekStart({<Week={">=$(vWeek)-6<=$(vWeek)'},ShipYear={'$(vYear)'}>}ShipDate)

So when you select any week I will automatcally show your measure for that week range that is current week and 6 week s back

[marcus_sommer · Accepted solution]

You need only a running and sorted week-counter over all data - within the master-calendar which is simply to create, for example with measurements like:

• interrecord-functions like peek() and previous()
• autonumber(Year&Week)
• calculation of: Year * 12 + Week

and then:

sum({< WeekFlag = {">=$(max(WeekFlag)-6)<=$(max(WeekFlag))"}>} Value) 

[mikegrattan]

Hi Marcus. This sounds promising...could you provide more details? I have the following Master Calendar in the app:

SalesTemp:

LOAD

min(ShipDate) AS minDate,

max(ShipDate) AS maxDate

RESIDENT

Sales;

LET MinShipDate = Num(Peek('minDate', 0, 'SalesTemp')); 

LET MaxShipDate = Num(Peek('maxDate', 0, 'SalesTemp')); 

DROP TABLE SalesTemp;

//Temp Calendar

temp_DateField:

LOAD 

$(MinShipDate) + rowno() -1 as Num,

   date($(MinShipDate) + rowno() -1 ) as TempDate

AUTOGENERATE $(MaxShipDate) - $(MinShipDate)+1;

//OrderDate Calendar

shipdate_calendar:

LOAD

TempDate as ShipDate,

Week(TempDate) AS ShipWeek,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as ShipDay  

Resident temp_DateField

ORDER BY TempDate ASC;

DROP TABLE temp_DateField;

[marcus_sommer]

It might be done in such ways:

load *, Year * 12 + Week as Flag1,
           autonumber(Year&Week) as Flag2,
           if(Week = previous(Week), peek('Flag3'), peek('Flag3') + 1) as Flag3;
load *, week(Num) as Week, year(Num) as Year;
LOAD $(MinShipDate) + rowno() -1 as Num,

   date($(MinShipDate) + rowno() -1 ) as TempDate

AUTOGENERATE $(MaxShipDate) - $(MinShipDate)+1;

Beside this a master-calendar shouldn't be derived from facts else created independently - containing all relevant past/future years/decades for the entire environment with all kind of period-informations (n versions of numbers and strings for each, counters, flags, ...). And all the target-applications pick then the specific fields and periods from it - it's much more simple & powerful and of course more performant.

[mikegrattan]

Marcus,

I appreciate the additional information, but I don't think I understand where this additional script would go. You mentioned an example earlier with a "WeekFlag" and I don't see that being created anywhere. Can you explain further please?

Thank you.

[marcus_sommer]

Flag 1 - 3 are (just different) ways to create the WeekFlag by skipping the resident-part and creating the period-information within n preceding-parts (it's just an example and not a fully calendar).

Meant was to create the calendar within an own and independent application which may create hundreds of different period-information and storing them in n qvd's. It's not necessary to create these information redundant in n application else everything needed could be simply picked from the qvd's.

[mikegrattan]

Bhushan,

Thanks for your suggestion. I did try it, but I got an error in set modifier ad hoc element list. Also, I believe that your approach would not work when the week is between 1 and 6, since it would result in a 0 or a negative number.

[mikegrattan]

Hi Marcus,

This post came the closest to my eventual solution, so I'll accept it. 

I ended up  doing the following:

• Added a calendar object to the sheet
• User selects a date in calendar, and with that date I calculate a six week starting date and the six week ending date and place them in variables
• Using the variables, I can use calculations such as the following in order to get a six week average:

(Num(Sum({<ShipDate_DayOfWeek={'Thu'},FloorShipdate={">=$(vStartSixWk)<=$(vEndSixWk)"}>}$(vMeasure))/6,'#,##0'))