9 Replies Latest reply on May 15, 2017 5:35 AM by Ashraf Chohan

# Help with Calculated Fields

Hi

I'm struggling with two things and would be grateful for any assistance, I've attached a packaged workbook with sample data.

1. Sheet 1

The data shows IDs of companies and whether they applied to be included in a directory (there have been 3 versions of the directory A,B and C with C being the latest). I'd like to create a calculated field which will allow me to split the IDs by whether they have previously been on the directory or whether they are new.

So if they were on either A or B (or on both) they will be classified as "Old" or if they're only on C they will be "New". I'd like create a calculated field so I can use it on a wider data set.

2. Sheet 2

We have an additional dimension which shows the size of a company. Where an organisation has more than one size listed for versions A and B I'd like to create a field which prioritises the value in this order 'large', 'medium', 'small', 'micro'. So for example if an organisation is being shown as 'micro' in version A and 'small' in version B the calculated field will show the organisation as being 'small'. Hope this makes sense.

Any help appreciated

Thanks

• ###### 1. Re: Help with Calculated Fields

hi Ashraf,

So on the first part I've used an LoD (and the alphabetic order of A, B and C) to determine the Old and New Ids...

[New IDs]

IF {FIXED [ID]:MAX([Version])} = 'C' THEN [ID] END

[Old IDs]

IF {FIXED [ID]:MAX([Version])} <= 'B' THEN [ID] END

On the 2nd part...how would you want to handle this example

so both A and B versions, both have 2 assignments...

If you let me know this, we can use a similar technique to the first part to solve it.

• ###### 2. Re: Help with Calculated Fields

Thanks Simon

I used a number 'search and replace' to change many of the IDs from the original source data (which is sensitive) which has inadvertently has resulted in several IDs ending up the same. In the original version an ID will only have a single value (or nothing) under each version.

Note that for Q1 I am looking for a single field which will tell me if an ID is either old or new. I'll also be interested to know (if possible) how to tackle this if the version values were static string values (e.g. 'spring', 'summer', 'autumn').

Thank you for your time

• ###### 3. Re: Help with Calculated Fields

OK...so ignoring the DQ issues, we can do something like this...

[Last Version per ID]

{FIXED [ID]:MAX([Version])}

and then we can use this field to only return a value where the Version = MAX (or Last) version. We can then use an LoD to put that value against every row for each ID

[Size Last]

{FIXED [ID]: MAX(IF [Version] = [Last Version per ID] THEN [Size] END)}

So what's going on here...the IF statement is either returning (for each row) either size (IF Version = MAX Version) or NULL (as we have no ELSE statement). It then assess this and takes the MAX of this value (for each ID) as the field is either NULL or Size, the MAX (or MIN for that matter) of NULL and something is always the something.

I had to search for a "good" example, to show you that it works...

One other thing to note...in sheet 2 you had filtered the data to versions A & B only. FIXED LoDs get calculated before any filters are applied, so in order to not return the C version size (for the IDs that had signed up to C) I had to "bump" the filter up the calculation pipeline (so it is applied before the FIXED LoD is calculated) by making the filter a "context" filter.

Now I've been using MAX as the versions are alphabetic (A,B,C)...and if your real world example are seasons (as strings) there are a few ways to go...probably the easiest will be to create a field that numbers the seasons in the order you want

IF [Version] = 'Spring' THEN 1

ELSEIF [Version] = 'Summer' THEN 2

ELSEIF [Version] = 'Autumn' THEN 3

ELSEIF [Version] = 'Winter' THEN 4

END

and then just use that field, and you can just use the MAX.

Hope that makes sense.

• ###### 4. Re: Help with Calculated Fields

Thanks Simon

I've got the second part working perfectly. Still unsure about Q1 - I'd like to create one dimension that I can place alongside the ID column which shows whether the ID is new or old as in this example

Thank you for your continued patience!

Ashraf

• ###### 5. Re: Help with Calculated Fields

Ah I see...it was the "I want to split...." that confused me (something that is very easily done). So even simpler

[Old/New]

IF {FIXED [ID]:MAX([Version])} = 'C' THEN 'New' ELSE 'Old' END

Let me know if that doesn't do the trick

• ###### 6. Re: Help with Calculated Fields

Thanks Simon, that's perfect. I've got just what I needed.

Really appreciate your time on this.

Best Wishes

Ashraf

1 of 1 people found this helpful
• ###### 7. Re: Help with Calculated Fields

Hi

I have a follow up question on this. Is it possible to adjust the calculated field for Old/New so only instances where the only entry in C is classed as New?

So, if an ID is in both A and C it will be classed as 'Old' but if an ID did not exist in either A or B but is in C it will be classed as 'New'.

Thanks

Ashraf

• ###### 8. Re: Help with Calculated Fields

hi Ashraf,

So lots of ways we can adapt the formula to do this, here's one;

[Old/New]

IF {FIXED [ID]:MAX([Version])} = 'C' AND {FIXED [ID]:COUNTD([Version])} = 1 THEN 'New' ELSE 'Old' END

So this adds an extra test to determine that it's MAX version isn't only C, but that it only has one Version (i.e. it only has C and nothing else).

Hope that helps

• ###### 9. Re: Help with Calculated Fields

Excellent. Thanks again Simon

Ashraf

1 of 1 people found this helpful