1. M1. lookup Id: LOOKUP(attr([Id]),-1)
2. M2. lookup Dom: LOOKUP(attr([Dom]),-1)
3. M3. Change/No Change:
if FIrst()=0 then 'No Change'
elseif attr([Id])<>[M1. lookup Id] then 'No change'
elseif attr([Id])=[M1. lookup Id] and attr([Dom])=([M2. lookup Dom]) then 'No change' else 'Change' END
Hope it helps.
Once again it's proofs when you are able to take a walk & "re-think" an issue;) Find my updated approach as reference below and stored in attached workbook version 10.2 located in the original thread.
1. M1. Dom Converted:
if [Dom]='Psychology' then 1
elseif [Dom]='Medicine' then 2
elseif [Dom]='Business, Management and Accounting' then 3
2. M2. Dom converted diff: ZN(SUM([M1. Dom Converted])) - LOOKUP(ZN(SUM([M1. Dom Converted])), -1)
3. M3. Change/No Change: if [M2. Dom converted diff]<>0 then 'Change' else 'No Change' END
4. Drag the required objects to the indicated locations
Hope it helps,
Thanks Norbert for your both answers, it taught me new things but I think neither of them can deal with the case when an ID has several domains (dom) and all of them are stable over time.
That's my fault, I focused on calculating changes and my fake data were lacking variety...
In the second version of the workbook I join, I created this example: an ID with the same 2 domains every year. The calculated field should indicate "no change".
What's more, I think the second option you proposed might be difficult to implement because I have 300+ different domains.
I hope you will be able to re-re-think the issue.
Thanks a lot
fake2.twbx 15.2 KB