topic Re: How to skip an expression when there is a zero value in part? in App Development

How to skip an expression when there is a zero value in part?

Thu, 29 Jun 2017 17:43:45 GMT

Here is the current code,

((Alt(sum(if(Month='January',GP))/sum(if(Month='January',[Extended Amount])),0)-(Alt(sum(if(Month='February',GP))/sum(if(Month='February',[Extended Amount])),0)

+Alt(sum(if(Month='February',GP))/sum(if(Month='February',[Extended Amount])),0)-(Alt(sum(if(Month='March',GP))/sum(if(Month='March',[Extended Amount])),0)

+Alt(sum(if(Month='March',GP))/sum(if(Month='March',[Extended Amount])),0)-(Alt(sum(if(Month='April',GP))/sum(if(Month='April',[Extended Amount])),0)

+Alt(sum(if(Month='April',GP))/sum(if(Month='April',[Extended Amount])),0)-(Alt(sum(if(Month='May',GP))/sum(if(Month='May',[Extended Amount])),0))))))

*-1)/5

So basically, I am taking January's GP % less February's + February's less March’s, etc.

However, if there are no sales in one of those months, it is skewing the data by either not subtracting anything or subtracting a full months number from 0. How do I make that particular part of the equation not factor in when one of the months has no sales?

Re: How to skip an expression when there is a zero value in part?

Anonymous — Thu, 29 Jun 2017 18:57:33 GMT

what is the alt doing ?

Re: How to skip an expression when there is a zero value in part?

Thu, 29 Jun 2017 19:00:35 GMT

It is turning a null result into 0. If I do not use the Alt function I get - as a result because the formula can't be calculated.

Basically, it is manifesting zero sales as 0 instead of -.

Re: How to skip an expression when there is a zero value in part?

Anonymous — Thu, 29 Jun 2017 20:05:31 GMT

You can do that in the data load editor. If([ExtendedAmount = Null(), 0) it should turn your null values into zeros. Then you won't have to handle it in the UI. It may help.