0

INVALID FORMULA Error when using CASE with Aggregation

Hi All

I have data like below

Item         Quarter         TotalCost

Item1       FY19 Q4       100

Item1       FY20 Q1        120

The Total Cost Column is a Formula column from Business Schema and has some further calculations behind it.

 

For my Dashboard I need to use conditional logic and I am using CASE.

CASE(Quarter = "FY20 Q1", TotalCost,0) 

If I directly use this CASE as a New Formula and then select Aggregation from Setting while creating Insight, I get an error value as INVALID FORMULA as a result of the above CASE statement.

If I am not aggregating then I don't get this error.

Also if wrap the above CASE inside SUM/MAX like

SUM(CASE(Quarter = "FY20 Q1", TotalCost,0) )

and then select aggregation, I don't get the INVALID FORMULA value. But I dont want to wrap inside aggregation function.

Is there a way to resolve this error? 

I have seen one post regarding this error on Community, which says that this feature is not supported in current versions. So I wanted to know if there is any work around or solution to this INVALID FORMULA Error.

 

Also I don't want to display Quarter in my final results, so Level Based Measures using groupBY is also not an option I believe as I would need to select Quarter as Dimension field in Insight if I put it in groupBY.

 

Thank you.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Ashwin Warrier Anurag Malik Amit Kothari  Could you please let me know if there is any workaround for this issue. In one other post I saw that the feature is not supported in the current version of Incorta (the updated on that post was 7 months back I believe)

    Reply Like
  • Yogesh, Can you share formula for "Total Cost".

    It is not recommended to use formula inside case statement. In certain scenario it will not work or return incorrect data.

    Reply Like
  • Anurag,

    Please see formula details below. There is quite a lot derivation behind.

     

    First Total Cost

    -> Total Cost = case(MATERIAL_COST > 0, MATERIAL_COST,BPA_COST > 0, BPA_COST, PO_COST > 0, PO_COST)

    Each of these Fields are further having below derivation:

    -> MATERIAL_COST =  

    IF(max(SCHEMA_NM.MPL_MV.WEIGHTED_MATERIAL_COST_AVG)=0,case(sum(SCHEMA_NM.MPL_MV.ON_HAND_QTY)=0,max(BUS_SCHEMA.BUS_VW.MATERIAL_COST), sum(BUS_SCHEMA.BUS_VW.TOTAL_MATERIAL_COST)/sum(BUS_SCHEMA.BUS_VW.ON_HAND_QTY)),max(SCHEMA_NM.MPL_MV.WEIGHTED_MATERIAL_COST_AVG)) 

    Then

    -> TOTAL_MATERIAL_COST = BUS_SCHEMA.BUS_VW..MATERIAL_COST * BUS_SCHEMA.BUS_VW.ON_HAND_QTY

     

    Similarly we have derivations for BPA_COST and PO_COST.

    Reply Like
Like Follow
  • 1 mth agoLast active
  • 3Replies
  • 32Views
  • 2 Following