Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ChrisB7
Contributor
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
sunny_talwar

May be use Dimensionality() function

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

darrell_tobin
Creator
Creator

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

ChrisB7
Contributor
Contributor
Author

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

ChrisB7
Contributor
Contributor
Author

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

sunny_talwar

You need to use it in the If Statement

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

darrell_tobin
Creator
Creator

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

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

what's your expected result?

ChrisB7
Contributor
Contributor
Author

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

ChrisB7
Contributor
Contributor
Author

Thanks Darrell.  I will keep it in mind