2 Replies Latest reply on Jul 31, 2018 1:49 PM by Jim Dehner

    Grouping rows based on a financial condition

    Kathryn Berry

      I cant share the data because of confidentiality but I am trying to apply a filter to the various details which are shown in a report. The filter is based on one measure, total capex, but I am also including other measures in the report, some calculated, some not and if I just apply a basic filter on the total capex it stops showing the other measures on the report and just shows nil. Is there an easy way to fix this?

       

      Ideally I would want to group projects within a dimension based on a criteria of total capex being greater than $20m. I have tried to do this within one dimension for example using the following formula to effectively show the project title if the total capex is >$20m but if not just group together under one heading.

      IF {fixed[Project Title]:SUM([2018 To 2022 Capex Required])}>20

      THEN [Project Title]

      ELSE "<$20m"

      END

       

      This is a bit random as to whether it works or not - most of the time it does but it is not 100% accurate.

       

      Any suggestions as to how I can get this to work please?

       

      Thanks for any help you can give

        • 1. Re: Grouping rows based on a financial condition
          Deepak Rai

          Please Create a Sample Example using Superstore data to help u faster.

          • 2. Re: Grouping rows based on a financial condition
            Jim Dehner

            Good afternoon

            Since we cant see your data it is difficult to provide anything but the most basic input -

            that sadid your foruma

             

            IF {fixed[Project Title]:SUM([2018 To 2022 Capex Required])}>20

            THEN [Project Title]

            ELSE "<$20m"

            END

             

            is structured to create a group of under $20m projects - and a group of individual programs that are each over $20m

             

            if you wanted to create 2 groups change the formula

             

            {fixed[Project Title]:   Min ( if  SUM([2018 To 2022 Capex Required])}>20

            THEN ">$20M"

            ELSE "<$20m"

            END  )}

             

            you may need to play with the syntax - don't have a way to check it out

             

            convert it to a dimension and now use it to group the over 20 M capex project titles - and use it in calculations and filters

             

            you can still list the individual projects as the next level in your viz

             

            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.