Help with calculated field (source field greyed out)

Josselin Granger shared this question 10 days ago
Awaiting Reply

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”

21c0faae9b97638c0433808f522a22ec


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.

52d5c50e8fa52a01651d306728f18896

What I would do next would be to calculate the minMargin by multiplying sales by minMarginRate … which I can’t.

93ceb78a09f01343d22a1893f02ff65c

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.

c045c7dac7ac45efab3fc4ff60dd3ee7

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

Comments (1)

photo
1

Hi Josselin,

I agree, this could be clearer. There are rules for what fields you can reference with calculated fields, depending on what you're building and whether you're using sub/master queries or fields from different views. It looks like that applies here. If you are referencing view fields, you can only use those when building the calculated field. Likewise fields from master and sub queries cannot be used with view fields. It's an either/or situation.

I hope that helps! I only recently came to understand this myself.

Kind regards,

Chris