Else if and Sum over

Question & Answer
1

I am trying to create the following formula:

if distinct_count_segment=0,
then sum total_cost_slim for each order_id,
else sum(total_cost_slim)/distinct count of segments

I tried with this and other variations but it doesnt work.

ifelse(sumOver({total_cost_slim}, {distinct_count_segment}=0),[order_id],{total_cost_slim}/{distinct_count_segment})

where distinct_count_segments=
distinctCountOver
(
{segment_name},
[{line_item_id}], PRE_FILTER
)

1 Like
2

Hello @Patrizia_Buompastor1 - Thank you for posting your query. Can you please share few sample rows and expected values? IT would be easy to replicate the use case at our end. Thank you!

3

Hi @sagmukhe ,

I am joining two tables to get the ‘total cost slim’ and as a result I get:

  1. the rows with full segment names show me duplicated total costs (so for this ones I do total_cost_slim/distinct count of segments per order id)
  2. rows with null segment names showing me other cost

What I want is to sum 1+2 →
for the rows with null segment name, take the related cost
for the rows with full segment name, take the cost / distinct count of segment per order

4

Hello @Patrizia_Buompastor1 - Thank you for your response. When you mention to take the “related cost” is it the “Order Total Costs” column from your snapshot and cost means “Order Total cost 1”. I am sorry if I am not making sense. In that case a bit more details on the expected values would be really helpful for the rows that you have shown in the above snapshot. Thank you!

5

Hi @sagmukhe,

My cost shoud only be calculated based on the column Total_cost_slim only but I have used the below columns to test.

order tot. cost 1 = {total_cost_slim}/(distinctCountOver({segment_name}, [{line_item_id}], PRE_FILTER) ) —> to try and sum the cost from duplicated rows

order total cost = (sumOver(sumIf({total_cost_slim}, {distinct_count_segment} = 0),[{order_id}])) —> to get the cost from null rows

so basically summing up the totals from the 2 columns but it doesnt let me do that. So i think the best way would just be to use a formula that gives me the total cost of null rows + total undeduplicated total from notnull rows.

image
image1436×332 45.6 KB

6

Hi @sagmukhe do you have any idea how I can solve this?

7

@Patrizia_Buompastor1 - Sorry for the late response. I still could not find line_item_id and distinct_count_segment columns from your snapshot of the sample dataset. Can you please share the snapshot of the dataset along with those columns as well? If they are calculated fields, can you please share the calculation details? Thank you!

8

Hi @sagmukhe , no worries I sovled it by doing this: ifelse(isNotNull({a_order_name}), ({total_cost_slim}/{distinct_count_segment}), {total_cost_slim})

1 Like