1 Reply Latest reply on Jun 27, 2018 9:12 AM by Jim Dehner

    calculating or filtering  one dimension based on NOT MATCHING  values in another dimension.  

    Jeff West

       

      I have data with these elements:.

       

      Project #, User, Date, Status (open, completed), Action (viewed, changed, approved).

       

      I want to be able to see which projects were never changed.   Each project will be looked at multiple times by different users.

       

      I was thinking I could do this with Sets but the logic is escaping me.  (Except shared members?)

       

      I can create a set that has all projects (set 1) and another set that has only viewed and approved projects (set 2) and then create a combined set that only shows projects that are in set 1 but not in set 2 but I can't get it to work in a filter.

       

      I think I need a calculated field somewhere.

       

       

      I also tried a Level of Detail but that expects an aggregated value so calculation is invalid.

       

      { FIXED  :

       

      if NOT  = "changed" then 1 else 0 end}

       

       

       

       

      Jeff West

       

      Legal Disclaimer :

      The information contained in this message may be privileged and confidential.

      It is intended to be read only by the individual or entity to whom it is addressed

      or by their designee. If the reader of this message is not the intended recipient,

      you are on notice that any distribution of this message, in any form,

      is strictly prohibited. If you have received this message in error,

      please immediately notify the sender and delete or destroy any copy of this message!

       

        • 1. Re: calculating or filtering  one dimension based on NOT MATCHING  values in another dimension.  
          Jim Dehner

          Hi Jeff

          I don't have a dataset to check this out (that is one of the reasons we like to have a twbx workbook even with dummy data) - so you may have to tweak it

           

          {fixed  [ project #] :sum(

           

          { FIXED      Project #, User, Date, Status    :   max (if NOT  = "changed" then 0 else 1 end )}  )]

           

           

          then you can use it as a filter set to 0 (i.e. never changed)

           

          or you can use it in a clac like

           

          if

           

          {fixed  [ project #] :sum(

           

          { FIXED      Project #, User, Date, Status    :   max (if NOT  = "changed" then 0 else 1 end )}  )] = 0 then 'Not Changed'

          else 'Changed' end

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.