I need help writing this calculated field

Question & Answer
,
1

Hi, I’m trying to create a calculated field similar to part/sum of parts but with a condition.
here is a picture of my table to give you an idea of how it looks like:

image
image1445×860 103 KB

I have multiple batches (shown is only 1) each batch consists of ingredients and a product (and maybe a co-product).

one KPI is to calculate what is called “Unitary Cost” which for ingredients is the Total Cost of the ingredient/batch cost and batch cost here is the sum of all ingredient’s “Total Cost” (only the ingredients).
However, if the row is for a “Product” then the Unitary cost is simply the batch total cost

here is my attempt:

image
image1085×403 13.3 KB

here are the other calculated fields involved in the expression above:

{Actual Qty Cost}:
{ACTUAL_QTY_IN_PRIMARY_UOM}*{GL_COST}


{Item Type}:
ifelse({ITEM_TYPE}=1,'Product',ifelse({ITEM_TYPE}=-1,'Ingredient','N/A'))


{Batch Total Cost}:
sumOver(sumIf({Actual Qty Cost},{Item Type}='Ingredient'),[{BATCH_NUMBER}])

How can I avoid the “Mismatched aggregation” error message? I tried to wrap everything in a SUM function but that did not work either. Please help.

2

Can you try with a PRE_AGG?

sumOver(ifelse({Item Type}='Ingredient',{Actual Qty Cost},0),[{BATCH_NUMBER}],PRE_AGG)
1 Like
3

Thank you Max, but I still don’t know how to apply two different expressions in one calculated field based on the {Item Type} value.

In another BI tool I could write something like this:

sum( 
     aggr(
                 If( {Item Type} = 'Ingredient', [Expression 1],
                      If({Item Type} = 'Product'', [Expression 2]
                       )
                    )
              , {Batch Number}
              )

)

I just don’t know how to write the equivalent expression to the above n QuickSight.

4

Are you saying you want to replace the value. Can you change my calculation to instead of returning 0 return the other column you want?

sumOver(ifelse({Item Type}='Ingredient',{Expression 1},{Item Type}='Product',{Expression 2},0),[{BATCH_NUMBER}],PRE_AGG)
5

Hi @Ali_B

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

8

Hi Max, Thank you for un-archiving this post so I can add my solution to it.

sumOver(ifelse({Item Type}='Ingredient',{Expression 1},{Item Type}='Product',{Expression 2},0),[{BATCH_NUMBER}],PRE_AGG)

The suggested expression above didn’t solve the issue. I wanted an expression that returns two different results depending on the “Item Type”.

here is what worked for me:

ifelse({Item Type}='Ingredient',{Actual Qty Cost},{Item Type}='Product',

sumOver(ifelse({Item Type}='Ingredient',{Actual Qty Cost},0),[{BATCH_NUMBER}],PRE_AGG)
,0)

wrapping the sumOver with an ifelse solved the problem.