Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cbracchi
New Contributor

Null measure for specific field(s)

Hi All,

I am very new to Qlik Sense.  I have tried many things, but without success.

I have the Field called Brick that contains a number of values (with no standard naming convention).  I would like it so that if the field Brick is used in a table that the call count becomes NULL, i.e. it is not applicable.  Having the total there of 59 is correct, but just not the detail. 

Bricks.PNG

It will be the case that I will have to do the same for other fields also for the same measure, for example another field called Type (This is just a prototype, so please go along with the poor naming conversions for now)

Any help with this would be greatly appreciated.

Thanks,

Chris

9 Replies
MVP
MVP

Re: Null measure for specific field(s)

May be use Dimensionality() function

=If(Dimensionality() = 0, YourExpression, Null())

darrell_tobin
Contributor

Re: Null measure for specific field(s)

Hi Christian,

If I want to show a dimension that has no data to measure I usually fix this at the data loader (data modelling level).

For example i have the same issue with Dates.  If no sales occurred on a given date then when reporting in QLik this is not shown, so i lod a table of ALL dates possible and connect this to the measure data.

In your case, the answer is to create a table in the data loader section that has

1. A field to join to your measure data such as date

2. All of the brick variables you have

Join this table to your measure data using the date field and the Brick field can then be selected as the dimension and will show even if you have no measure data ( ensure show values with null is ticked)

  

DateBrick
01/01/2018Brick 1
01/01/2018Brick 2
01/01/2018Brick 3
01/01/2018Brick 4
01/01/2018Brick 5
01/01/2018Brick 6
01/01/2018Brick 7
01/01/2018Brick 8
01/01/2018Brick 9
01/01/2018Brick 10
01/01/2018Brick 11
01/01/2018Brick 12
01/01/2018Brick 13
01/01/2018Brick 14
02/01/2018Brick 1
02/01/2018Brick 2
02/01/2018Brick 3
02/01/2018Brick 4
02/01/2018Brick 5
02/01/2018Brick 6
02/01/2018Brick 7
02/01/2018Brick 8
02/01/2018Brick 9
02/01/2018Brick 10
02/01/2018Brick 11
02/01/2018Brick 12
02/01/2018Brick 13
02/01/2018Brick 14
03/01/2018Brick 1
03/01/2018Brick 2

Pls mark complete and like if this is useful

cbracchi
New Contributor

Re: Null measure for specific field(s)

Hi Sunny,

Thanks, I looked at Dimensionality() function, but it just returns the number of dimensions for the current row, whereas I think i need to know specifically know if the Brick dimension is there.

Thanks,

Chris

cbracchi
New Contributor

Re: Null measure for specific field(s)

Hi Darrell,

Thanks for your help.  Sorry, but I think my question wasn't clear enough.

If I have a table where someone uses Brick (dimension field), Sales Units and Call Count (measures) then I would like the value for CallCount to be NULL, i.e. don't show anything.

There is a relationship present in the data model that I do need to keep, so do you know of a solution through some expression that I could use?

Thanks,

Chris

MVP
MVP

Re: Null measure for specific field(s)

You need to use it in the If Statement

If(Dimensionality() = 0, YourExpression, Null())

darrell_tobin
Contributor

Re: Null measure for specific field(s)

Hi Christian,

To the best of my knowledge you cannot create rules to change the way a table behaves based on which fields are added to it by the user.

You can make a field show null using the null() function.

Below is example:

=if(

     aggr(count(TariffId),Date)=0,                                      //Pre checks if count value is 0

    null(),                                                                           // If count =0 then shows null instead

    Count(TariffId)                                                             //else do the count

)

You may find an extension to help in Branch, but I doubt it.

Darrell

agigliotti
Honored Contributor II

Re: Null measure for specific field(s)

what's the expression you are using for "Call Count" column?

what's your expected result?

cbracchi
New Contributor

Re: Null measure for specific field(s)

Hi Andrea,

current expression is simply

Count([%Calls Key]) - this is simply meant to be a count of the rows in a table.  This could equally be a SUM(CallCount) where CallCount is simply a value of 1 per row in that calls fact table.

In terms of expected results. if Brick field is EVER in a table/Chart and used with CallCount then CallCount should be NULL.

Thanks,

Chris

cbracchi
New Contributor

Re: Null measure for specific field(s)

Thanks Darrell.  I will keep it in mind