Best practice in Qlik Sense

[pe_rusnak]

Hi Everyone,

I would like to ask you several questions, which I am not pretty sure how it works:

1. If I create master item such as count(OrderID) and use this master item within several visualizations. Is this item calculated only once or serveral times based on how many times I used it?
2. If I use count(OrderID) within the first visualization and count( OrderID ) for the second one. It will be calculated once or not?...Please see that I have spaces for the second one.
3. Would you guys recommend to store variable definition within the external file or it depends on the scenario? As far as I know there are some pros as - central place for editing, sharing among apps, but i can see more cons as - no visitability of expression for end users, need to reload app everytime the definition is changed, all variables remains in editor until you manually delete them, need to check syntax and correctnes before storing into external file,...
4. How to replace null values for a dimension for whatever visualization to be able to use tooltip correctly. I tried bar chart and used this expression to get rid off the null values (categorize them into another existing values). You can see the expression below and the result as well (Tooltip is broken, I tried also aggregate measures in tooltip but still do not work and I think Qlik does it natively.

aggr(Only(If(IsNull(ConsentCategory), 'NA',ConsentCategory )),ConsentCategory)

Appreciate help from experienced audience.

Thanks

PRU

[fosuzuki]

1. Qlik Sense caches the calculations to have better performance and reuse them always when possible. If you use one master measure in two charts, and for each chart you have different dimensions, then it will calculate twice.

2. I once heard from a Qlik webinar (long time ago, don't remember who said it) that Qlik will leverage the cache only if the expression is exactly the same, spaces and casing included. So in your example, it would calculate twice.

3. I agree with your pros and cons. I'd say that the best benefits for storing variables externally would be the possibility for version control and better organization of the project.  For me the answer may vary according to the situation.

4. Please be aware that calculated dimensions may affect the performance, and you should avoid them when possible. In most cases, the calculated dimension's logic can be migrated to the ETL.

[pe_rusnak]

Many thanks for the replies @fosuzuki. All your answers make sense to me but I would like to add a comment to 4th bullet.

I totally understand your point but I think I cant do it as I have more dimensional values than values used in the fact table. Which means that visualization for all customers will return null once the row for the particular employee is missing. This means that I need to use the calculated dimension to be able to categorize null values into another customer or name it on my own or simply select ignore null values which is not intended. Am I right?

[fosuzuki]

Your solution to treat the null values is not wrong . If you're not having performance issues, I'd say to leave it as is.

The only concern is with performance when the data volume is big, and the calculations are complex.

So as a general best practice, I always try to move the calculations in the ETL. In your scenario, you could manually add a row in Customer table for the NAs.

[pe_rusnak]

Hi @fosuzuki , I think that in my star schema is not posible to do that as I have fact table and dimensional table linked by id and lets imagine that I have two rows in fact table and two customerid are used, but in the dimensional table I have three customers, which means that in case of number of facts per customers I will get null value for the one missing customerid in fact table. This is caused by the model itself and definitely need to use condition in dimension. 

[fosuzuki]

I'm sorry. I was referring to the case when there is data in the fact table, but not in the dimension table.

But you are thinking the other way: there is data in the dimension but not in the fact table, right?

Then, I guess that your expression to treat null values in ConsentCategory dimension will not work well, because you are only treating the dimension value, not the missing data in the fact table.

To treat missing fact table values, there are two approaches:

1. Remove the "unneeded" data from the dimension. In some cases, you can consider that this is just garbage. For instance, if you have sales fact table for the last 2 years, why bring an inactive customer who didn't buy anything in the last 2 years? You can do it with the EXISTS() function.

2. In the chart's properties panel, you can go to Add-ons > Data handling > uncheck Include zero values. This will omit the dimension values which have zero as a result from the measure.

[pe_rusnak]

Hi,

sorry for late response, but it is as you are saying : But you are thinking the other way: there is data in the dimension but not in the fact table, right?

I think that treating null values is correct as I am treating missing value as well. Because null values means, that not all of the dimension from dimensional tables are present in fact table.

Totally aggre with your approaches, but none of them are possible fe. because end users would like to see all dimensional values. But instead of - (null values) would like to see 0 for example. And of course they would like to categorize all unused dimensional table into another category. Example for this are targeted customers. If there is no customer row in fact table (target), they would like to add those customers into category 'Not Avaialble', which is present within the datamodel. Qlik does only hide null values, but not find and replace - or it can do, but not with working tooltip.