Aggregate combined with condition

geertdeman — Sat, 16 Nov 2024 04:17:11 GMT

I need to create a formula to color code red for any 2 months below threshold within 4 rolling month period. The value that needs to be verified for each month is % on time: sum(on time)/sum(total). Below is a simplified dataset. Example: if the user selects period 2019-05, the formula must check the % on time for may, April, March and February. If 2 of those months have a % on time below a certain threshold I need to color the background of a column red.

The output table is grouped by name and period and has the value % on time but only the selected period is displayed (while the calculation must check the max of selected period and previous 3 months)

Name	month	total	on time
A	2019-01	1	1
A	2019-02	1	1
A	2019-01	1	0
A	2019-01	1	1
A	2019-04	1	1
A	2019-06	1	1
B	2019-01	1	0
B	2019-02	1	1
B	2019-01	1	0
B	2019-03	1	1
B	2019-04	1	0
B	2019-06	1	1

topic Aggregate combined with condition in App Development

Aggregate combined with condition