Create a new variable in webi as SUMIFS() in excel

[former_member597137]

Hello everyone,

I'm a bit stuck with the creation of a formula.

I have a first dashboard with 1 column for a product dimension, 1 for the total sales (all clients included) and 1 with an measure (variable) saying "Yes" if the client distributes the product or "No" if not.

From this Table, I want to create a cross-table Client x Client with a single measure as follow:

For each combinaison Client 1 x Client 2 , Sum the Sales of the Client 1 where Client 1="Yes" and Client 2="No"

In excel I can do it with this formula

=SUMIFS(($A:$A;$B:$B;"Yes";C:C;"No")

Is there a way to do it in Webi ? I can't find a solution.

Thank you for your help

Marc

[amitrathi239]

try this.

=if((([Client="Client 1") and ([V_Yes/No="Yes")) then sum ([Sales Objects]) elseif ((([Client="Client 2") and ([V_Yes/No="No")) then sum ([Sales Objects])) else 0

[amitrathi239]

try similar to below formula. here i am assuming V_Yes/No is variable and Client is object with values Client 1/ Client 2

=if((([Client="Client 1") and ([V_Yes/No="Yes")) and (([Client="Client 2") and ([V_Yes/No="No"))) then sum ([Sales Objects]) else 0

[amitrathi239]

have you tried with If-Else in webi

e.g.

=if([Client 1]="Yes") and ([Client 2]="No")) then sum ([Sales Objects]) else 0

[mhmohammed]

Try something like this
=Sum(If([Client 1]="Yes") Then [Sales Objects] Else 0)

Let us know how it goes!

Thanks,
Mahboob Mohammed

[former_member597137]

Thank you for your reply.

It's not working as Client is a single dimension with multiple values.

Is it because of context operators?

[amitrathi239]

in Which object "Yes/No" values are stored? or is it a variable?

[former_member597137]

It's a variable