3 Replies Latest reply on Nov 24, 2018 7:50 AM by Frederique Bordignon

# Comparing multiple-values dimensions between columns

Hello,

I have the follwing data:

I would like to create another table where each cell mention whether there is a change or not comparing to the year before. Something like this:

I would appreciate any kind of help..

Thanks a lot.

f

• ###### 1. Re: Comparing multiple-values dimensions between columns

Hi Frederique,

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.

Regards,

Norbert

• ###### 2. Re: Comparing multiple-values dimensions between columns

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

END

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,

Regards,

Norbert

• ###### 3. Re: Comparing multiple-values dimensions between columns

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

f