1 Reply Latest reply on Mar 14, 2018 2:59 PM by Joe Oppelt

    Calculated Field to apply for all records with same value of particular field

    Paul Lim

      Hello - is it possible to create a calculated field such that if a condition is met on one record, the calculated field will display a particular value on all records with the same value on a different field? This is a mouthful of a question so I think an example would clarify.

       

      For example, let's say I have three records in my underlying data like in the image below. I want to create a calculated field titled "Group Desc". I want this calculated field to check for any records with blank sales. If it does, I want this field to label all records with same Store ID (in this case 123) with the string 'New Store'.

       

      example.PNG

        • 1. Re: Calculated Field to apply for all records with same value of particular field
          Joe Oppelt

          If you have a FIXED LOD calc at the lowest level of granularity, you can identify any row that has an empty sales amount.

           

          { FIXED [Record ID] : IF ISNULL(SUM([Sales])) then 1 END }

           

          (If you don't really have a Record ID, then you would do

           

          {FIXED [Store ID], [YEAR] : ...

           

          or whatever it would take to identify unique rows.)

           

          Now you can do this:

           

          { FIXED [Store ID] : if SUM( the other fixed calc) >0 then "New Store" END }

           

          And if you wanted, you could actually nest the first calc into the second if you just wanted to do it in one calc.