Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: How to calculate min and return 0 if null exists

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
Highlighted
Master II
Master II

Re: How to calculate min and return 0 if null exists

Please go thru below link and hope this helps:

Generating missing data:  Generating Missing Data In QlikView

Highlighted
Master II
Master II

Re: How to calculate min and return 0 if null exists

Hi,

Like this?

Capture.JPG

Highlighted
Not applicable

Re: How to calculate min and return 0 if null exists

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
Highlighted
MVP
MVP

Re: How to calculate min and return 0 if null exists

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

Highlighted
Master II
Master II

Re: How to calculate min and return 0 if null exists

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