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

    Help with Calculated Fields

    Ashraf Chohan



      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


        • 1. Re: Help with Calculated Fields
          Simon Runc

          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
            Ashraf Chohan

            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
              Simon Runc

              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



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


              Hope that makes sense.

              • 4. Re: Help with Calculated Fields
                Ashraf Chohan

                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


                Screen Shot 2017-05-11 at 16.33.17.png

                Thank you for your continued patience!



                • 5. Re: Help with Calculated Fields
                  Simon Runc

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



                  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
                    Ashraf Chohan

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


                    Really appreciate your time on this.


                    Best Wishes


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



                      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'.




                      • 8. Re: Help with Calculated Fields
                        Simon Runc

                        hi Ashraf,


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



                        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
                          Ashraf Chohan

                          Excellent. Thanks again Simon



                          1 of 1 people found this helpful