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 calculate min and return 0 if null exists

Hi All,

Here's my scenario. I have a transaction file grouped by category and date, but not all categories happen every day. When I calculate minimum number of transactions, qlikview is not considering the absent numbers as 0. Below is an example data.

CategoryDateOrder Amount

A

1/1/2017$10
A1/2/2017$5
B1/2/2017$5

I want to return something like below. For Category A, return minimum of order amount for these 2 days, which will be $5. Same for Category B. Since there is no transaction on 1/1, it should consider it as $0 and therefore the minimum is $0. How can I do this?

Categorymin(Order Amount)
A$5
B$0
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like

Category If(Count(DISTINCT Date)= Count(TOTAL DISTINCT Date), min([Order Amount]),0)
2
A2
B0

Disable 'suppress zero values' on presentation tab.

edit: sorry, I've used previously attached QVW, with different test data for A.

View solution in original post

5 Replies
trdandamudi
Master II
Master II

Please go thru below link and hope this helps:

Generating missing data:  Generating Missing Data In QlikView

devarasu07
Master II
Master II

Hi,

Like this?

Capture.JPG

Not applicable
Author

Thirumala,

The attached post is definitely helpful, but not quite in my situation. I want Qlikview to treat null as 0 when calculating minimum. I was able to get minimum by category by date. But if I only want minimum for a category throughout all days, it omits the null again and does not return 0 as minimum.

Here's my scenario. I have a transaction file grouped by category and date, but not all categories happen every day. When I calculate minimum number of transactions, qlikview is not considering the absent numbers as 0. Below is an example data.

CategoryDateOrder Amount

A

1/1/2017$10
A1/2/2017$5
B1/2/2017$5

I want to return something like below. For Category A, return minimum of order amount for these 2 days, which will be $5. Same for Category B. Since there is no transaction on 1/1, it should consider it as $0 and therefore the minimum is $0. How can I do this?

Categorymin(Order Amount)
A$5
B$0
swuehl
MVP
MVP

Maybe like

Category If(Count(DISTINCT Date)= Count(TOTAL DISTINCT Date), min([Order Amount]),0)
2
A2
B0

Disable 'suppress zero values' on presentation tab.

edit: sorry, I've used previously attached QVW, with different test data for A.

trdandamudi
Master II
Master II

Stefan, has already provided the solution and please use that and you will get the desired result. Hope this helps.