Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Category | Date | Order Amount |
---|---|---|
A | 1/1/2017 | $10 |
A | 1/2/2017 | $5 |
B | 1/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?
Category | min(Order Amount) |
---|---|
A | $5 |
B | $0 |
Maybe like
Category | If(Count(DISTINCT Date)= Count(TOTAL DISTINCT Date), min([Order Amount]),0) |
---|---|
2 | |
A | 2 |
B | 0 |
Disable 'suppress zero values' on presentation tab.
edit: sorry, I've used previously attached QVW, with different test data for A.
Please go thru below link and hope this helps:
Generating missing data: Generating Missing Data In QlikView
Hi,
Like this?
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.
Category | Date | Order Amount |
---|---|---|
A | 1/1/2017 | $10 |
A | 1/2/2017 | $5 |
B | 1/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?
Category | min(Order Amount) |
---|---|
A | $5 |
B | $0 |
Maybe like
Category | If(Count(DISTINCT Date)= Count(TOTAL DISTINCT Date), min([Order Amount]),0) |
---|---|
2 | |
A | 2 |
B | 0 |
Disable 'suppress zero values' on presentation tab.
edit: sorry, I've used previously attached QVW, with different test data for A.
Stefan, has already provided the solution and please use that and you will get the desired result. Hope this helps.