3 Replies Latest reply on Sep 19, 2018 3:53 PM by Hari Ankem

    Grouping Based on Condition

    Scott Kolar

      Cant post the original content due to privacy issues, however, I attached a mock excel file with the same probelm I'm trying to solve

       

      Right now if I group 2 data points within 1 measure it combines it with an Or condition.  In this example it would show 5.  I want the group to use an "And" condition and only count 2 orgs that have both excel and word. And then I want to plot that group on a graph to show that 2 facilities are using both excel and word. In english it would be: If organization contains Word and Excel then 1, else 0.

       

      Capture.PNG

        • 1. Re: Grouping Based on Condition
          Hari Ankem

          Here you go:

          1.png

           

          Here are the calculated fields I have created:

           

          Count of Excel: {SUM(IF [Software]="Excel" THEN 1 ELSE 0 END)}

           

          Count of Word: {SUM(IF [Software]="Word" THEN 1 ELSE 0 END)}

           

          Count of Orgs with Excel and Word:

          IF (

          {FIXED [Organization]:SUM(IF [Software]="Word" THEN 1 ELSE 0 END)}

          +

          {FIXED [Organization]:SUM(IF [Software]="Excel" THEN 1 ELSE 0 END)}

          )=2 THEN

              1

          ELSE

              0

          END

           

          Count of Excel or Word:

          IF [Software]="Word" OR [Software]="Excel" THEN 1 ELSE 0 END

           

          A 2018.2 workbook is attached. Hope this helps.

          • 2. Re: Grouping Based on Condition
            Scott Kolar

            Yes that worked and I was able to get a count, however, a calculated field doesn't seem to integrate well with my existing graph that I was making.  Here is what my graph looks like currently.  So as you can see I have 579 excel's and 389 words, when I applied the formula it took it down to 247.  I can do counts for each of the orgs but then I won't be able to apply filters.  Ideally I would just want to use the group feature based on the org. Any advice?

            Untitled.png

            • 3. Re: Grouping Based on Condition
              Hari Ankem

              I will need to see your data to check what's going wrong. But, one thing I am noting in your image is that you haven't added your filters to context. Since the formulas use FIXED LOD's, you should add your filters to context.