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

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.

5 Replies
Highlighted
Master II

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

Generating missing data:  Generating Missing Data In QlikView

Highlighted
Master II

Hi,

Like this?

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

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.

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