Skip to Content

Formula Grouping of Name Object

Hello - Kindly assist.

I have a formula - Trying to group some Name by my defined Name preferred and Group some Names together as well. See my formula below. The issue is - the query does not bring out the GROUP and TOTAL. Is it because I am calling it twice in the query?

The query only brings out the Name below:

CAT, BABOO, MIA.

However, the GROUP and TOTAL not coming up in the list - Note that the GROUP and TOTAL is to add up the Names that I already referenced in the formula. I need assistance to see the Name Object display. Thanks ALL

CAT

BABOO

MIA

GROUP

TOTAL

=If ([Name]InList("CMO");"CAT";If ([Name]InList ("BAT";"BABO"); "BABOO"; If([Name] InList("MIA"); "MIA"; If([Name] InList("BAT";"BABO";"CMO");"GROUP";If([Name]InList("CMO";"BAT";"BABOO";"MIA");"Total";[Name])))))

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Apr 06, 2016 at 03:46 PM

    Hi,

    That's normal, as you said you specified twice CMO, BAT, BABO and MIA.

    So the first valid where clause are taken into account.

    Can you give more context?

    When you are testing [Name], is it for the whole dataset?

    Based on your test it assume that you want to have GROUP if at least the 3 values ("BAT";"BABO";"CMO") are present once in the dataset.

    And you want TOTAL if you have all the data ("CMO";"BAT";"BABOO";"MIA") at least present once in the dataset?

    Am I correct?

    If this is the case, Irecommend you to count each occurrence.

    Then your fomula will look like:

    =If ([CMO] > 0 and [BAT]> 0 and [BABO] > 0 and [MIA] > 0) Then "Total"

    Else If ([CMO] > 0 and [BAT]> 0 and [BABO] > 0) Then "GROUP"

    Else If ([MIA] > 0) Then "MIA"

    Else If ([BAT]> 0 or [BABO] > 0 ) Then "BABOO"

    Else If ([CMO] > 0) Then "CAT"

    Else [Name]


    Didier

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Shila,

      I understand that my table shows exactly what you need, but the problem is when you create a variable using if statement with your conditions, you'll not see the data multiple times because the DataSets would've satisfied a condition and grouped accordingly.

      How an If statement works is, if a value say (CMO) has satisfied the first condition, it'll be labelled as CAT and it won't be fed to the If statement again to be labelled as GROUP or TOTAL. Does that make sense? The solution would be to create multiple tables one below the other and show dupe data using different If Condition results. Or you'll have to create 2 combined queries and then bring dupe data. Make sense?

      What do you mean by DataSet? Just that one value or something else?

      Again, please attach a snapshot from Excel with sample data including sample manufactured measures, what you want to see and how you want to see.

      Thanks,
      Mahboob Mohammed