Script to add a rolling count per dimension over 21 day period with irregular dates

Marcus_Svensson — Tue, 07 Feb 2023 19:44:25 GMT

Hi!

I have a table in a dashboard that lists trainers and their horses competing in a specific race. In this table I have calculated the number of races each of the horse trainers have won during the preceding 21 day period. This is then the basis for a performance rank. The formula looks like this and works only when I have selected a "T-Date" and a "Race" as I use the GetFieldSelection() in the formula: Aggr(NODISTINCT Count({1<[T-Date]={"<=$(=Date(GetFieldSelections(T-Date)-1,'YYYY-MM-DD'))"}>*1<[T-Date]={">$(=Date(GetFieldSelections(T-Date)-21,'YYYY-MM-DD'))"}>*1<Placement ={1}>}[Trainer]), [Trainer])

What I am struggling with is to add the same calculation in a different table containing all races during 3-4 years (see picture). I understand that this formula is not possible to compute row by row in a chart and therefore I have tried to look for an approach to do it in the script. Right? Here is an example of what I want to be able to achieve: a rolling 21-day count of number of races won by trainer in order to rank their performance for each race; so that I can have this table in a dashboard.

I have looked at rangecount, iterative loads using peek and such things but cannot figure out how to do it without syntax problems or invalid expressions. Anyone who have an idea of how to do it?

Re: Script to add a rolling count per dimension over 21 day period with irregular dates

vinieme12 — Wed, 08 Feb 2023 08:21:16 GMT

Create an AsOf Table, where each date is associated to previous 21Dates

refer below, or post some sample data in excel

https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130

Re: Script to add a rolling count per dimension over 21 day period with irregular dates

vinieme12 — Wed, 08 Feb 2023 08:23:17 GMT

row by row evaluation is also possible , but creating an AsOf table is the most performant solution as the prior would be slower in comparison

topic Script to add a rolling count per dimension over 21 day period with irregular dates in App Development

Script to add a rolling count per dimension over 21 day period with irregular dates

Re: Script to add a rolling count per dimension over 21 day period with irregular dates

Re: Script to add a rolling count per dimension over 21 day period with irregular dates