cancel
Showing results for 
Search instead for 
Did you mean: 

How can I add total category in a dimension?

Former Member
0 Kudos

I am trying to create a dimension that will show:

If Peter, then Area 1

If John, then Area 2

If both Peter and John, then Country 1

I wrote the following variable using IF & Else IF.

But it is not working.


=If([Salesmen Name]="Peter") Then ("Area 1")


Else If([Salesmen Name]="John") Then ("Area 2")


Else If([Salesman Name] InList ("Peter";"John")) Then ("Country 1")


End results will look like this:


AreaSales
Area 1100
Area 2200
Country 1300


Could you please advice me how I can achieve this....?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Hayden,

Does the dataset always contains both Peter and John?

Does it contains more names? If yes, what do you want to do with additional names?

Your if expression cannot work because it will always be true fro John or Peter and not for the 3rd expression.

To provide you an correct answer can you send a screenshot of your dataset to better understand what you want to display.

Thanks

Didier

Former Member
0 Kudos

1. I do not have access to back-end so trying to do this front-end.

2. [Salesman Name] has 6 names.

1. Peter

2. John

3. Matthew

4. Keith

5. Michael

6. Tay

3. I need to show in 1 column the following:

Area 1 (Peter) 

Area 2 (John)

Country 1 (Peter & John)

Area 3(Matthew)

Area 4(Keith)

Country 2 (Matthew & Keith)

Area 5 (Michael)

Area 6 (Tay)

Country 3 (Michael & Tay)

Former Member
0 Kudos

Hi Hayden

Please try to create a concatenated variable with the Salesman and the variable that you have created with if else condition.

Regards,

Amala.S

Former Member
0 Kudos

OK thank you for your advice.

I googled Concatenated Variable and found link below:

I will keep researching.

Former Member
0 Kudos

Hi Hayden,

Create a variable "Area" with the following formula:

=If [Name] InList("Peter";"John") Then "Country 1"

Else If [Name] InList("Matthew";"Keith") Then "Country 2"

Else If [Name] InList("Michael";"Tay") Then "Country 3"

Then create a table with Area, Name and Sales.

Create a break on Area as following:

Drag & Drop the Area from the break row under the Name and add Sales in front of the Area break row as following:

Then erase the Area title (do not remove the column).

Change the Area column backgorund color to White and Font to 2.

Now reduce the Area column size to make it invisible.

Last edit the Area breack and uncheck "Break header".

Edit the table properties and check "Show table headers".

Here is the final result.

Didier

Former Member
0 Kudos

Didier!

Wow thank you so much for this wonderful trick!!!!!!

Answers (0)