Help with calculated field (source field greyed out)
I’m really struggling with Calculated Fields. I really have a hard time understanding when and why I can or cannot use a field in a calculated field (beside data format obviously).
Even weirder, it seems I don’t have the same behavior all the time.
What would be reeeeaaaaally useful from a ux point of view, would be to have to a tooltip explaining why a field is greyed out in the calculated field editor.
Let’s use an example
- I have two views based on two datasource
- View 1 contains accounting data
- View 2 contains min and max values for gross margin, based on analytics data
- Both sources are mySQL, populated via a transform flow and stored on yellowfin server
- Our goal is to verify, per analytics, if the margin value is coherent
- If it’s too high, it doesn’t mean that we made too much money, it means that the data is incorrect
The beginning is pretty straightforward : I create a report based on view 1 (accounting), with
- The business Unit
- The analytics Code
- SUM(Sales) --> Sales is simple calculated field (works fine) : CASE WHEN account BETWEEN value1 AND value2 THEN Amount ELSE 0 END
- SUM(Purchases) --> Same : CASE WHEN account BETWEEN value1 AND value2 THEN 0 ELSE Amount END
- SUM(Amount) --> Renamed “Margin”
I then add a subquery based on View 2, with a join on analytics code, which allows me to retrieve the maxMarginRate and minMarginRate for each Analytics code.
What I would do next would be to calculate the minMargin by multiplying sales by minMarginRate … which I can’t.
If figured this was because “Sales” is aggregated whereas minMarginRate is not … but if I add MIN(minMarginRate) in the formula it does’nt work.
And if I add the agregation on the minMarginRate column it doesn’t work either.
Then I tried something else … instead of using Sales which was my previously calculated field, I tried with “Margin” … and now I can multiply it with the field from my other view.
It’s not at all what I want, but as I say, I’m desperately trying to figure out the rules leading to a field being impossible to use in a calculated field.
I thought it was because I can’t use a calculated field which I created on a view, to then be reused in another calculated field from another view … but then I tried to multiply the amount by the minMarginRate without success.
It’s driving me nuts, can someone help me ?
Is there anywhere in the documentation a list of the prerequisites to be respected before using a calculated field ?