topic Re: Need to dynamically filter a six week range, based on selected week and year in App Development

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

mikegrattan — Wed, 29 Jan 2025 22:25:19 GMT

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:

Find the WeekStart day of Week and Year to get the ending date of the Six Week period.
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!

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

Bhushan_Mahajan — Thu, 30 Jan 2025 05:02:33 GMT

@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

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

marcus_sommer — Thu, 30 Jan 2025 09:08:08 GMT

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)

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

mikegrattan — Thu, 30 Jan 2025 17:56:12 GMT

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;

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

marcus_sommer — Fri, 31 Jan 2025 06:29:08 GMT

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.

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

mikegrattan — Fri, 31 Jan 2025 18:08:50 GMT

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.

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

marcus_sommer — Sat, 01 Feb 2025 10:49:45 GMT

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.

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

mikegrattan — Mon, 03 Feb 2025 19:25:01 GMT

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.

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

mikegrattan — Wed, 05 Feb 2025 18:13:14 GMT

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'))