Help with calculated field (source field greyed out)

Hello,

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 ?

Thanks