0 instead of Null for Date Dimensions / Set Analysis for Dimensions?

[Anonymous]

Hi all,

I have an issue with which I struggle and I hope to find help here.

I have a date dimension, associated to other dimensions and values. If filtered, I want to keep all dates in the date dimension.

This is my data structure

MyDate	Dim	Stock
01.01.2018	a	5
02.01.2018	a	7
03.01.2018	a	12
04.01.2018	a	3
05.01.2018	a	15
03.01.2018	b	20
05.01.2018	b	50

As you can see, the Dim “a” has values on each date (1.1. – 5.1.). Dimension ‘b’ however has no values on 1.1., 2.1. and 4.1.2018.

I would like to have a KPI visualization in Qlik Sense which, when filtered only by Dim “b” returns as min Stock value by date the number 0, as there has been no Stocks on the 1.1., 2.1, and 4.1 (currently, due to the filter and the NULLs for the other dates, I get 20 as a min value when filtering "b". Please also note that I achieved this in a table visualization via the Set expression "sum(Stock) + 0*Sum({1} Stock)", but I need this in a KPI visualization, i.e. a scalar).

The KPI should furthermore be filterable by date, i.e. if I filter “b” and the date 5.1.2018 I would like the min to be 50 (and not 0).

Help would be greatly appreciated

[petter]

By having a Master Calendar you should be able to achieve what you want. Have you tried this?

[jomar_ebonite]

Use a Master Calendar or

Use "Alt" function

Alt ( Sum(Stocks) ,0 )

[sunny_talwar]

It might help if you are able to Generating Missing Data In QlikView

[Anonymous]

Thanks Sunny for this interesting document, I will have a look at it

[Anonymous]

Interesting function, good to know thanks. But unfortunately it does not fulfill this specific goal. Filtering by b will still return 20 as the min value. For the moment it seems the first part of your comment (actually using a master calendar which I try to avoid) seems to be the way to go.

[Anonymous]

Hi Petter, thanks. Yes, I guess I ll have to do that though I am reluctant as my data source is already quite big and I try to avoid adding records for every variation of Dim (in fact, Dim represents many further Dimensions and I have a intervalmatch in there too).

If no other possibility exists I ll implement this and feedback again end of this week or next week.