Need alternative calculation to calculate total volume which should not get change of any filter change

Question & Answer
,
1

I have created a calculation field which is total volume

sumOver(({inflow_asin}), [{gl_product_group_desc}, merchant], PRE_FILTER)

which would give us total volume irrespective of any filter.

This total volume is combination of categorization/uncategorization/miscategorization.

but we consider uncategory/miscategory is the defect. Hence we need to identify the defect calculation which would be

defective% = uncat+miscat/(total_volume which is category/miscategory/uncategory)

The reason why I have to calculate the total volume in this way is:
sumOver(({inflow_asin}), [{gl_product_group_desc}, merchant], PRE_FILTER),

We also need to use these categorization bucket in the filter. if we filter only uncat or miscat the defective% calculation’s denominator should not change because the denominator is combination of category/miscategory/uncategory which is total_volume

for example if total volume is 30 which is the breakdown of cat: 10, miscat: 10 and uncat:10 then the defective% will be uncat+miscat/total volume which would be 20/30 = 67%, if we change the filter to only miscategorization then it should be 10/30 = 33%.

now this total volume calculation by default set this as min when put this field well. because if we select sum it does not right total volume number and it randomly aggregates to produce sum huge number.

Since this total volume is min(total_volume) when it comes to other filters applying like GL and merchant, calculation gets collapsed.

Reason if it gl_biss and the data source is 1P and 3P as merchant, both 1P and 3P has separate total volume. when I select the filter merchant both 1P and 3P the expected output is adding up of 1P and 3P total volume and make the defective% calculation accordingly. Instead the total volume finds the min value of 1p and 3p total volume and this leads to incorrect defective% calculation.

First table shows one of the view which gives expected output Second table is my other requirement which should be this expected output Third table is what I ended up to it which is not correct

s-4
s-41036Ă—589 17.3 KB

Could someone please help me out, is there any other alternative solution to get the expected output that I have given in the excel sheet.

Thanks, Raja

2

Hello @Raja !

I think the problem is stemming from having Total Volume set to min which is making the expected output table choose the min value no matter. When you say that the Total Value is set to min because the sum creates an incorrect value, are you setting it to min in the field well aggregation?

One thought I had is to try nesting your LAC-W calculation in a LAC-A function to force it to give sum correctly for Total Volume. You could try something like this:
sum(sumOver(({inflow_asin}), [{gl_product_group_desc}, merchant], PRE_FILTER), [categorization])

1 Like