3 Replies Latest reply on Jun 15, 2018 1:31 AM by Simon Runc

    Creating A Set or Group Basis Calculation

    Shivi Bhatia

      Top 9 exceptions.pngThis is for an accounting process - the process matches debit and credit in books of records and wherever there is a mismatch they call it Break.

       

      For every break they have a reason called as Exception.

       

      I need to create a viz which should show top 9 reasons for breaks and rest all should be 1 category and the data has more than 24 such categories.

       

      So total 10 break reason [Exception] would come this can’t be done manually because these exceptions change daily basis break volume.

       

      I have tried creating a set of Top 9 Exception types and then tried creating a calculated filed :

       

      if [Exception Type]!= [Top 9 Exception Reasons] then "others"

      else [Top 9 Exception Reasons]

      end

       
      This throws as error saying: Cant compare string and boolean values.

       

      Please advice.

        • 1. Re: Creating A Set or Group Basis Calculation
          Simon Runc

          hi Shivi,

           

          So when you create a set it is just a boolean expression (In or Out) for the elements which meet (for in) or don't meet (for out) the condition of being in that set.

           

          As such all you need is

           

          if  [Top 9 Exception Reasons] then [Exception Type]

          ELSE 'Others'

          END

           

          Let me know if that doesn't do the trick

          • 2. Re: Creating A Set or Group Basis Calculation
            Shivi Bhatia

            Thanks Simon, this worked like charm. Really what i was looking for.

             

            Last thing: i know this is very tricky but suggest if there is a workaround. I am adding a pic of the viz i have created, but suggest if this can be sorted where the category "Others" comes at the bottom of the chart, i can manually do it but with the new data it will not work.

             

            Please advice.

            • 3. Re: Creating A Set or Group Basis Calculation
              Simon Runc

              hi Shiva,

               

              Glad it did the trick.

               

              So with regards sorting there are several ways we could go. One way is like this (I've recreated your situation in the attached) but also created this calculation

               

              [Top 3 Other Sorter]

              IF [Top 3 Categories] THEN [Sales]

              ELSE [Sales]-1000000 END

               

              You can then use this field to sort the dimension (you may need to make the -10000000 number larger depending on your data). This way the Top 3 retailer their sort, but Other will always be last.

              1 of 1 people found this helpful