Sum values from different dimensions

[Former Member]

Hi everyone

I need help since I could not deal with this problem by myself. I have tried different formulas but did not receive any result.

I have 3 dimensions.

Store EH02, EH 03, EH 04 etc.

Cost Center SRV 201, SRV 301, SRV 401 etc.

Departmanet IB01, IB02, IB03 etc.

I now how to define names or sum values in one dimension. For example Sum(Value, where ([store]="EH02",[store]="EH03" )

But now I need to sum values using all three dimensions. Since different combinations have different meanings.

For example

Store EH02 and Cost Center SRV 201 and Department IB01 = customers

Store EH 02 and Cost Center SRV 301 and Department IB02 = dealers

Store EH 03 and Cost Center SRV 201 and Department IB01 = foreign customers etc.

Please help me to define variable for each of these combinations.

My table looks like

Monhes	Store	Cost center	Department	Amount
January	EH02	SRV 201	IB01	500
January	EH02	SRV 301	IB01	100
January	EH03	SRV 201	IB02	500
January	EH03	SRV 301	IB03	450
January	EH02	SRV 301	IB02	300
February	EH01	SRV 201	IB02	200
February	EH02	SRV 201	IB02	400
February	EH05	SRV 301	IB03	200

[Former Member · Accepted solution]

Hi Ofeliya,

Please Used this code

=If (([Store]  inlist( "EH03","EH04")) And ([Cost Center]  inlist( "SRV-201","DST-201")) Then "Standart customers" 

Also made variable as details of [Store] or [Cost Center] if this not work then use [Store] and [Cost Center] in column and hide them.

[Former Member · Accepted solution]

Dear Raju

I have tried to use Inlist while creating variable. I received error message and decide to write all codes separately dividing them by elseif.

below you can find error screen

[Former Member · Accepted solution]

Hi

I believe you get this error when separating list values with (,) comma - Try using (;) semicolon.

[Former Member · Accepted solution]

=if([Store] inlist("EH03";"EH04")) And ([Cost Center] inlist ("SRV-201";DST-201")) then"Customer"

[Former Member · Accepted solution]

Hi,

Please replace , with ;

[Former Member]

Dear Dinesh, Raju and Christoffer

Thank you for your response. Problem is solved with semicolon as you stated. In Universe design I used to use comma (,), that is why also in webi I tried the same action.

[Former Member]

close thread if you got solution

[Former Member]

I am sorry but I don't know how to do it.

[Former Member]

closing should be done based on selecting a helping answer are correct answer

Regards

Dinesh

[sateesh_kumar1]

Hi Ofeliya,

Create a variable like

=If([Store]="EH02" And [Cost center]="SRV 201" And [Department]="IB01") Then "Customers"

Else

If([Store]="EH02" And [Cost center]="SRV 301" And [Department]="IB02") Then "Dealers"

Else

If([Store]="EH03" And [Cost center]="SRV 201" And [Department]="IB01") Then "Foreign Customers"

use this variable in table to get the sum for each type of customer.

[Former Member]

Hi Sateesh

I really appreciate your response. I have tried this way. I have begun from customers and create variable like this 

=If (([Store] = "EH03") And ([Cost Center] = "SRV-201")) Then "Standart customers" ElseIf

(([Store] = "EH03") And ([Cost Center] = "DST-201")) Then "Standart customers" ElseIf

(([Store] = "EH04") And ([Cost Center] = "SRV-201")) Then "Standart customers" ElseIf

(([Store] = "EH04") And ([Cost Center] = "DST-201")) Then "Standart customers"

Unfortunately it returns #multivalue

I also tried to filter each column, but it didnt work. Filter was applied for all table.

[Former Member]

Hi Sofiyeva,

                   As per your requirement ,

mohens	Store	Cost Center	Department	Amount
Feb	EHO1	SRV201	IB02	200
Feb	EHO2	SRV201	IB02	400
Feb	EHO5	SRV301	IB03	200
Jan	EHO2	SRV201	IB01	500
Jan	EHO2	SRV301	IB01	100
Jan	EHO2	SRV301	IB02	300
Jan	EHO3	SRV201	IB02	500
Jan	EHO3	SRV301	IB03	450

Create a [Variable]==If([Store]="EHO2" And [Cost Center]="SRV201" And [Department]="IB01") Then"Customer" ElseIf([Store]="EHO2" And [Cost Center]="SRV301" And [Department]="IB02") Then"Delar"  ElseIf([Store]="EHO3" And [Cost Center]="SRV201" And [Department]="IB02") Then"Foriegn Customer" ElseIf([Store]="EHO3" And [Cost Center]="SRV301" And [Department]="IB03") Then"C_S" Else "Standard Customer"

mohens	Store	Cost Center	Department	Amount	Var_c_s_d_1
Feb	EHO1	SRV201	IB02	200	Standard Customer
Feb	EHO2	SRV201	IB02	400	Standard Customer
Feb	EHO5	SRV301	IB03	200	Standard Customer
Jan	EHO2	SRV201	IB01	500	Customer
Jan	EHO2	SRV301	IB01	100	Standard Customer
Jan	EHO2	SRV301	IB02	300	Delar
Jan	EHO3	SRV201	IB02	500	Foriegn Customer
Jan	EHO3	SRV301	IB03	450	C_S

As per your requirement  change this SQL

Regards

Dinesh