topic Re: Cumulative flag=1 in App Development

Cumulative flag=1

QuentinDeci — Mon, 06 Sep 2021 13:30:07 GMT

Hello everybody,

I have a question for you:

I search to calculate the cumulative number of days where the flag = 1 (if the flag = 0 the cumulative stop).

For exemple:

mag	cug	date	flag
1	1010	09/06/2021	1
1	1010	09/05/2021	1
1	1010	09/04/2021	1
1	1010	09/03/2021	0
1	1010	09/02/2021	1
1	1212	09/06/2021	1
1	1212	09/05/2021	1
1	1212	09/04/2021	0
1	1212	09/03/2021	0
1	1212	09/02/2021	1

So the table with the cumulative calcul (based on today() so change at every load) will be :

mag	cug	Nb Cumulative
1	1010	3
1	1212	2

Thank you

Have a good day

Re: Cumulative flag=1

chrismarlow — Mon, 06 Sep 2021 16:53:19 GMT

Hi,

I would start with joining the last date with zero in flag to the data (so code similar to below) & then you have choices depending what you want to do (you could create a new flag that is only 1 where date>maxdate and sum, you could aggregate that into a separate table ...).

Cheers,

Chris.

data: load * Inline [ mag, cug, date,flag 1, 1010, 06/09/2021,1 1, 1010, 05/09/2021,1 1, 1010, 04/09/2021,1 1, 1010, 03/09/2021,0 1, 1010, 02/09/2021,1 1, 1212, 06/09/2021,1 1, 1212, 05/09/2021,1 1, 1212, 04/09/2021,0 1, 1212, 03/09/2021,0 1, 1212, 02/09/2021,1 ]; maxzero: Load mag, cug, max(date) as maxdate resident data where flag=0 group by mag, cug; left join (data) Load * resident maxzero; drop table maxzero;

Re: Cumulative flag=1

QuentinDeci — Tue, 07 Sep 2021 07:59:06 GMT

Hey Chris,

Thank you very much for your quicky answer !

Sometimes i search difficult solution (whith previous() or while...) but the simplicity is the most time the best!

Have a good day

Re: Cumulative flag=1

chrismarlow — Wed, 08 Sep 2021 19:38:49 GMT

Thanks, glad it helped.