7 Replies Latest reply on Jun 23, 2016 1:58 PM by Joe Oppelt

# Assigning a numerical value to each row and then using this value in a calculation to create a new measure

Hello,

The title is a bit confusing, but I am trying to assign a decimal value to each row (customer segment) and then use this in a different calculation where I multiply this decimal value by another measure (i.e. sales). This product (i.e. decimal value x sales) will become a new measure.

For example: assigning a decimal value to each customer segment would look like this:

• IF [customer name] = 'James' THEN 0.15
• IF [customer name] = 'Bob' THEN 0.15
• IF [customer name] = 'Frank' THEN 0.20

Then, I want to take these assigned values and multiply them by the sales for each customer.

This will create a new measure that I want to include in the viz.

Thanks,

Scott

• ###### 1. Re: Assigning a numerical value to each row and then using this value in a calculation to create a new measure

To clarify, the numerical (decimal) value that I am assigning to each customer segment is NOT an arbitrary number. It is a numerical value that measures each segment's CPC (cost-per-click) rate.

• ###### 2. Re: Assigning a numerical value to each row and then using this value in a calculation to create a new measure

Do it exactly as you envision it.

There is a CASE statement:

Case [customer name]

when "bob" then .15

when "frank" then .22

end

Or use ELSEIF

If [customer name] = "Bob" then .15 elseif

[customer name] = "frank" them .22

end

Either syntax allows unlimited cases.  First one in the list to get satisfied ends evaluation of the calc, so put the most common one at the top for best execution.

2 of 2 people found this helpful
• ###### 3. Re: Assigning a numerical value to each row and then using this value in a calculation to create a new measure

Hi Joe,

Thank you for your help!! Do you know if I can use the CONTAINS function within a case statement? I ask because many different segments assigned to a single CPC value. If so, do you know what this would look like?

Thanks!!

• ###### 4. Re: Assigning a numerical value to each row and then using this value in a calculation to create a new measure

if [customer name] = "Thelma" then .44 elseif

CONTAINS([customer name],"obert") then .16 elseif

[customer name] = "Roberta" then .39 elseif

[customer name] = "Snoopy" then .3 elseif

...

this is where you need to be careful.  When you start doing CONTAINS and not just straight equivalences, sometimes a value can satisfy multiple clauses in long ELSEIF statements or CASE statements.  First one to get satisfied WINS!  Make sure you want that to happen.  In the example above, it would never get to "Roberta" because the CONTAINS would snatch that one up first.

• ###### 5. Re: Assigning a numerical value to each row and then using this value in a calculation to create a new measure

...

when CONTAINS(([customer name],"obert") then .16

...

EDIT:  THIS WILL NOT WORK.  I left this entry here for continuity of discussion, but CONTAINS won't work like this in a CASE statement.

• ###### 6. Re: Assigning a numerical value to each row and then using this value in a calculation to create a new measure

I apologize for the confusion, Joe. I meant is there a way to use contains within a case statment with multiple customer names in it.

For example (and this may not be possible, so please let me know):

CASE [Customer Name]

when CONTAINS(("Bob","Alex", "Scott", Frank", "Tom"])) then 0.15

when "Alexander" then 0.20

....

End

Or do I have to separately list out each customer name as its own line?

• ###### 7. Re: Assigning a numerical value to each row and then using this value in a calculation to create a new measure

I'm off the mark with telling you to use CONTAINS like that in the CASE statement.

Use the ELSEIF construct if you want to use CONTAINS.

So

if [customer name] = "Thelma" then .44 elseif

CONTAINS([customer name],"obert") or

CONTAINS([customer name],"Scott") or

CONTAINS([customer name],"Alex") or

then .16 elseif

[customer name] = "Roberta" then .39 elseif

[customer name] = "Snoopy" then .3 elseif

...

end

1 of 1 people found this helpful