INVALID FORMULA Error when using CASE with Aggregation
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.
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 =
-> 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.