topic Re: Set expression - calculation based on state and id in App Development

Set expression - calculation based on state and id

Micki — Thu, 23 Dec 2021 18:28:44 GMT

Hi guys, Could please help,

Could not build set expressions to calculate in the table only last state 'Active' when after was none State 'New' based on week date

Id	State	Set expression	Date
1	Active	0	week1
1	New		week1
3	Active	0	week2
3	New		week2
5	New		week3
5	Active	1	week3

Result should be 1

Many thanks!

Could you please take a look @sunny_talwar ?

Re: Set expression - calculation based on state and id

sunny_talwar — Thu, 10 Sep 2020 20:52:22 GMT

How do you determine if Active came first or New? Id and Date (week) seems to be same for both Active and New rows?

Re: Set expression - calculation based on state and id

Micki — Fri, 11 Sep 2020 06:22:48 GMT

Hi, sorry i didn't mention that instead of date i have Timestamp, so I sorted by ID + THEN week number based on Timestamp

Re: Set expression - calculation based on state and id

sunny_talwar — Fri, 11 Sep 2020 11:22:52 GMT

So, you can try one of the two

Sum(Aggr( If(FirstSortedValue(State, -TimeStampField) = 'Active', 1, 0) , Id)) or Count(DISTINCT {<Id = {"=FirstSortedValue(State, -TimeStampField) = 'Active'}>} Id)