# Custom mode - calculated variances returning a number

**URL:** <https://pug.phocassoftware.com/t/custom-mode-calculated-variances-returning-a-number/802>\
**Category:** Ask the Community\
**Created:** [November 17, 2018, 8:26am UTC](https://pug.phocassoftware.com/t/custom-mode-calculated-variances-returning-a-number/802 "2018-11-17T08:26:18Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![JonKemp](https://sea1.discourse-cdn.com/flex015/user_avatar/pug.phocassoftware.com/jonkemp/32/334_2.png) [@JonKemp](https://pug.phocassoftware.com/u/JonKemp)\
**Post date:** [November 17, 2018, 12:19pm UTC](https://pug.phocassoftware.com/t/custom-mode-calculated-variances-returning-a-number/802/3 "2018-11-17T12:19:30Z")

</div>

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](https://us1.discourse-cdn.com/flex015/uploads/phocassoftware/original/1X/e1db8aa0dfd185d5dfb86520d971b15e73973aa9.png)  
 ![image](https://us1.discourse-cdn.com/flex015/uploads/phocassoftware/original/1X/baa7cd63009a7bdfef9f2f79ea308ae1942ff064.png)  
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](https://us1.discourse-cdn.com/flex015/uploads/phocassoftware/original/1X/2d018d221f5210dd3614885ce53f95b1d5889e08.png)

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](https://us1.discourse-cdn.com/flex015/uploads/phocassoftware/original/1X/9757031445b63afc849c42420ae32f1c765f392d.png)

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 🙂  
Jon

---

_[View the full topic](https://pug.phocassoftware.com/t/custom-mode-calculated-variances-returning-a-number/802)._