Calculation Functions

Hi,

Is there a way to use ‘CASE WHEN’ statements and other more complex methods in the calculation fields?

Right no my Gross Profit % for one month is NaN as the last month’s revenue has not been included yet (is 0). This causes the graph of Gross Profit % to not show up at all.

Hi @kai,
A few options at your disposal.

  1. Can it be done as an calculated measure in the DB designer?
    This is mine:
    image
  2. Can you use custom mode to produce the same output?
    Like this:
  3. What if I need to calc on a number that changes?
    This is the use case where static calcs like the above are not acceptable, so you are after if the value is over 50 then do this, else do that. This can be achieved in custom mode by a means of SQL commands. I must stress the case statement approach here will only deal with the numerical result for inspection, i.e. the case will not work on the dimension name so case = ‘Fred’ will NOT work but Case > 50 WILL work. here is a detailed write up I did a year or so ago: Percentage share in Custom Mode
    Hope this helps,
    Jon

Hi @kai

If the case statement has too many variables, it might be easier to do it in Sql. I usually do my case statements and Sql it’s easier to manage.

When it come to calculation Phocus does a great job but it all depends on how complex your calculations are.