Progressive measure

[Max75]

Hallo everyone

I need to create a progressive measure that calculates how many times content_bbus_log is assigned the value NEW_CONDITIONS_SET and user_name_bbus_log is assigned the value U073976.

Everything should be grouped by contract_number and should consider only the first occurrence of the day (event_date_bbus_log).

Thank you!

contract number	event_date_bbus_log	content_bbus_log	user_name_bbus_log	Rank
5000000004	19/03/2021 13:42:52	NEW_CONDITIONS_SET	U048456
5000000004	19/03/2021 13:44:52	MESSAGE_TO_DEALER	U048456
5000000004	19/03/2021 17:30:58	MESSAGE_TO_DEALER	SPSL264
5000000004	19/03/2021 17:33:07	MESSAGE_TO_DEALER	SPSL264
5000000004	19/03/2021 18:26:07	PROP_STATUS_CHANGE	SPSL264
5000000004	04/06/2021 22:14:21	PROP_STATUS_CHANGE	SYSTEM
5000000004	04/06/2021 22:29:28	PROP_STATUS_CHANGE	SYSTEM
5000000004	29/06/2021 09:48:14	PROP_STATUS_CHANGE	SYSTEM
5000000004	23/09/2021 10:16:24	NEW_CONDITIONS_SET	U073976	1
5000000004	23/09/2021 10:17:04	NEW_CONDITIONS_SET	U061895
5000000004	01/07/2022 09:15:58	NEW_CONDITIONS_SET	U073976	2
5000000005	18/03/2021 12:37:09	WORK_PROPOSAL	SPSL264
5000000005	19/03/2021 14:02:25	INQUIRIES_REQUEST	U048456
5000000005	19/03/2021 14:02:25	PROP_DATA_CHANGE	U048456
5000000005	19/03/2021 14:04:17	NEW_CONDITIONS_SET	U048456
5000000005	22/03/2021 12:22:46	PROP_DATA_CHANGE	SPSL264
5000000005	22/03/2021 15:37:08	INQUIRIES_REQUEST	F18542A
5000000005	22/03/2021 16:22:45	NEW_CONDITIONS_SET	U073976	1
5000000005	27/03/2021 08:56:41	NEW_CONDITIONS_SET	U073976	2
5000000005	27/03/2021 09:01:42	NEW_CONDITIONS_SET	F39854B
5000000005	29/03/2021 17:52:59	NEW_CONDITIONS_SET	U073976	3
5000000005	31/03/2021 17:26:59	NEW_CONDITIONS_SET	U073976	4
5000000005	31/03/2021 17:29:01	NEW_CONDITIONS_SET	U073976
5000000005	31/03/2021 18:06:01	PROP_STATUS_CHANGE	SPSL264
5000000005	16/06/2021 12:20:31	PROP_STATUS_CHANGE	SYSTEM
5000000005	16/06/2021 12:20:32	PROP_STATUS_CHANGE	SYSTEM
5000000005	17/06/2021 12:02:01	NEW_CONDITIONS_SET	U073976	5
5000000005	17/06/2021 12:02:21	INTERNAL_NOTE	U073976
5000000005	01/07/2022 09:52:20	NEW_CONDITIONS_SET	U073976	6

[Max75]

in set analisys. Sorry!

[marcus_sommer]

It's not completely possible with a set analysis because the restriction to the first date needs a row-level evaluation. A solution may then look like:

count({ < F1 = {'x'}, F2 = {'y'}>} F3) * -(Date = max(Date))

which may depending on the object-dimensionality needs to embedded within an aggr() to be calculated within the wanted scope.

Beside this I suggest to consider to generate the essential information already within the data-model, maybe with a self-join on a group by load to max. Date against the key-fields or with interrecord-functions within a sorted resident load.