Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
May be use Dimensionality() function
=If(Dimensionality() = 0, YourExpression, Null())
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)
Date | Brick |
01/01/2018 | Brick 1 |
01/01/2018 | Brick 2 |
01/01/2018 | Brick 3 |
01/01/2018 | Brick 4 |
01/01/2018 | Brick 5 |
01/01/2018 | Brick 6 |
01/01/2018 | Brick 7 |
01/01/2018 | Brick 8 |
01/01/2018 | Brick 9 |
01/01/2018 | Brick 10 |
01/01/2018 | Brick 11 |
01/01/2018 | Brick 12 |
01/01/2018 | Brick 13 |
01/01/2018 | Brick 14 |
02/01/2018 | Brick 1 |
02/01/2018 | Brick 2 |
02/01/2018 | Brick 3 |
02/01/2018 | Brick 4 |
02/01/2018 | Brick 5 |
02/01/2018 | Brick 6 |
02/01/2018 | Brick 7 |
02/01/2018 | Brick 8 |
02/01/2018 | Brick 9 |
02/01/2018 | Brick 10 |
02/01/2018 | Brick 11 |
02/01/2018 | Brick 12 |
02/01/2018 | Brick 13 |
02/01/2018 | Brick 14 |
03/01/2018 | Brick 1 |
03/01/2018 | Brick 2 |
Pls mark complete and like if this is useful
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
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
You need to use it in the If Statement
If(Dimensionality() = 0, YourExpression, Null())
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
what's the expression you are using for "Call Count" column?
what's your expected result?
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
Thanks Darrell. I will keep it in mind