Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

richafels
New Contributor

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

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

6 Replies
MVP
MVP

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

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

jomar_ebonite
New Contributor III

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

Use a Master Calendar or

Use "Alt" function

Alt ( Sum(Stocks) ,0 )

MVP
MVP

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

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

richafels
New Contributor

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

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

richafels
New Contributor

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

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.

richafels
New Contributor

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

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.

Community Browser