Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

3 Replies
Anonymous
Not applicable
Author

what is the alt doing ?

Not applicable
Author

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 -.

Anonymous
Not applicable
Author

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.