4 Replies Latest reply on Sep 20, 2018 5:52 AM by Robertino Bonora

# Divide each row by a total from another connection

Hey guys i have the next question:

I have a calculated field that is something like this:

CASE [Category]

WHEN '1' THEN

IF Sales <= 100 THEN 'In' ELSE 'Out' END

WHEN '2' THEN

IF Sales <= 100 THEN 'In' ELSE 'Out' END

...

END

With a lot more of others options of category but all with the same if condition. My problem is that i have another connection with a table that has 10 rows and a column that is named SLA with this values (1,2,3,4,5,6,7,8,9,10) ok?

Well what i want to do is in the case to compare in each IF condition something like this IF Sales <= 55 THEN 'In' ELSE 'Out' END being 55 the sum of the 10 rows in the second connection.

This is possible? how can i solve this??

Thanks

Robertino

• ###### 1. Re: Divide each row by a total from another connection

Could you please attach sample data as twbx and illustrate problem and expected result with using value example in that data.

Thanks,

Shin

• ###### 2. Re: Divide each row by a total from another connection

Thanks for the quick response Shinichiro, i attached the twbx  so you can see it.

What im expecting is:

- Inside the [Detail] calculated field im using a parameter to see SLA stages or SLA acum, in this case what is giving me troubles is SLA acum so focus on this one

- SLA_Acum uses the parameter SLA and the [I/O_ACUM] calculated field, please focus on this one, here i want to replace the number 103 that im using in every IF condition. Im wanna to put there the total of SLA that can i obtain from the secondary connection called "SLA" and using the column called SLA, the sum of all the rows in this column that gives me the total of 102 (Sheet 2)

Thankss

• ###### 3. Re: Divide each row by a total from another connection

You can create 2 calculated columns dummy in each of the sources and give it value 1 and make it dimension.

Now blend your 2 sources with dummy coumn and now you can use it further as you required.

Attaching workbook.

• ###### 4. Re: Divide each row by a total from another connection

Hi Ankit, thanks for the response, but this still doesn't help me.

I need to use that sum(SLA) from the second connection inside the calculated field I/O_ACUM from the main connection replacing each "103" value from that calculated field.

Thanks!

Robertino