Custom mode - calculated variances returning a number

3

Hi @Garney,
If I understand correctly, you want a column to report a specific value when the calculation value = x, but the knack is it needs to be dynamic based on user selection?

If so you will need to have an additional custom column with a case statement in it, which inspects your values. This can be achieved as follows:

Example of Current Order Qty - Previous Order Qty = Variance
image
image
So far so, good, now we want to do this:
If variance > 10,000 then 5 else 0
Go back into your custom mode and do the following:

image
image.png714×334 11.9 KB

The import part is where you normally do Measure A - Measure B, this needs to be a case statement. The syntax of this is:
^case WHEN ( {a}-{b} )> 10000 THEN 5 ELSE 0 END
The end result is this:
image

Things to note:

  1. Case statement is limited to I think 6 where clauses, so if you can try to do as much as you can in the SQL stream first.
  2. Using case statements in this way requires “{}” (braces) around each variable i.e. {a} - {b}

Pretty nifty element that I think @IT_Support should write a detailed article on? As I am sure there are some weird and wacky things we can do with this useful addition.

Hope this helps, have a great weekend :slight_smile:
Jon