Formula Grouping of Name Object

[Former Member]

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])))))

[Former Member]

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

[Former Member]

Thanks Didier - I realize it takes account the first if Clause. However your formula is referencing the called text for "Name"

The [Name] is the Object with the DataSet. So if you can please help me here with the formula using the [Name] Object since this is for the whole Dataset. Your formula is using data from the [Name] object which is invisible because its within the [Name] object. I hope I am not confusing you.

=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])))))

-------------------------------------------------------------------------------------------------------------------------------------------------------

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

[mhmohammed]

Hi Shila,

Where do you want to see those new groups GROUP and TOTAL? Do you want to see them in the prompt window when you refresh the report? Or, do you want to see them in a table in the report with the total of measure for all those values as in the below pic?

Thanks,

Mahboob Mohammed

[Former Member]

Hi Shila,

What is [Name]? Is it a dimension?

If I understand the dataset can contain the values: CMO, BAT, BABO, MIA.

If I understand your formula you want to display:

• Total, if the dataset contains all the values
• Group, if the dataset contains CMO, BAT and BABO
• MIA, if the dataset contains MIA
• BABOO, if the dataset contains BAT and BABO
• CAT, if the dataset contains CMO

If this is the case I suggested that you create a measure per type of data such as:

MIA: =Count([Name]) Where (Name] = "MIA")

And then you can use the formula I gave you in the previous post to create your groups.

I assumed that [Name] is the dimension and contains the values CMO, BAT, BABO, MIA.

Didier

[Former Member]

Hi Didier - I get error using the Count Function; You clearly understand what I need to achieve here. I will appreciate your help in getting this.

Mahboob - your table shows how I want the list should be in a TABLE Format not Prompt under Name and Not the Amount. The question is, how do I achieve that? How can I see those listings under Name. I need to group some dataset under the Name Dimension so they represented by CMO, BAT, BABO, MIA, BABOO, GROUP, TOTAL.

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])))))

[mhmohammed]

Hi Shila,

Can you attach a snapshot of how it should look in the report (with a mockup in Excel)? What is the use of showing GROUP and TOTAL? What's the requirement.

Thanks,

Mahboob Mohammed

[Former Member]

HI Mahboob, your table shows Exactly what I need to see under the NAME

I was able to achieve below 3 so far because the if statement am using only sees the first conditions as Didier mentioned.

NAME

CAT

MIA

BABOO

Under My Name Dimension, there are the following DataSets = CMO, BAT, MIA, BABO

What I want is

Give me CAT where dataset is CMO

Give me MIA where dataset is MIA

Give me BABOO where dataset are BAT and BABO

Give me GROUP where datasets are BAT, BABO and CMO

Give me TOTAL where datasets are BAT, BABO, CMO, MIA

From the above the Name list should look like below. Thanks for your HELP!! I badly need this

CAT

MIA

BABOO

GROUP

TOTAL

[mhmohammed]

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