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

Grouping Based on Condition

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. • 1. Re: Grouping Based on Condition

Here you go: 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

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? • 3. Re: Grouping Based on Condition

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.