Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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?

Tags (2)
3 Replies
kellymcgrady
Contributor

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

what is the alt doing ?

Not applicable

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

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

kellymcgrady
Contributor

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

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.