Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikel_de
Creator
Creator

Problem with values in a straight table

Hi guys,

I have a problem with a straight table that shows zeros instead of calculating the desired measure. I am rocking the following data model (test sample):

Data:

LOAD * INLINE [

Code,Document,Order,Date

A100,1111,O100,10.05.2016

A200,1111,O200,15.05.2017];

Documents:

LOAD * INLINE [

Document,Validity_Start,Validity_End,Value_Planned,Value_Actual

1111,01.01.2015,31.12.2019,100000,150000];

Divisions:

LOAD * INLINE [

Code,Division

A100,South];

Let me explain the data:

  • The Data table contains a Document and Orders booked against the document. Each Order has a Code and a creation Date.
  • The Documents table contains info about the Document - Validity Start, Validity End, Planned Value, Actual Value
  • The Division table shows which of the company's Divisions created the Order

So everything is cool until I put this into a table. Then if there is a document that has more than one order and a missing division, the measures show zero:

1.PNG

This can be avoided by including Order as an additional dimension:

2.png

However, I do not want to do this as in the real dataset one document might have hundreds of orders booked against it on the same date and the table becomes huge. Therefore, the level of detail that I need is as shown in the first table.

The best solution I have come up with is to use Aggr() like this:

3.PNG

This solves my problem but kind of slows down the app, which I do not like...

So, two questions:

  1. Why Qlik behaves like this and does not calculate the values correctly in the first table?
  2. Do you think that my solution using Aggr() is acceptable or there is a better way? I would like to keep the calculation in the front end without changing the script.

Thank you!

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

To avoid letting the Division mess up your Sums you can simply use the expressions:

Sum(TOTAL <Document> Value_Planned)

Sum(TOTAL <Document> Value_Actual)

These sums are independent of Division and should not take this dimension into consideration.

View solution in original post

6 Replies
kkkumar82
Specialist III
Specialist III

Hey Mike

I think there is no point in showing the second record as it doesn't have a division associated, its like a full outer join

but if the business asks then we have to show it ...

if you really want you can suppress the second record so that it doesn't come up, as you said in real situation you might be ended up showing multiple records which may not be give any value to the business

petter
Partner - Champion III
Partner - Champion III

To avoid letting the Division mess up your Sums you can simply use the expressions:

Sum(TOTAL <Document> Value_Planned)

Sum(TOTAL <Document> Value_Actual)

These sums are independent of Division and should not take this dimension into consideration.

petter
Partner - Champion III
Partner - Champion III

or even simpler - you could left join in the Division names to the Data table like this:

LEFT JOIN (Data) 

LOAD * INLINE [ 

Code,Division 

A100,South];

Then you could keep your simple Sum(Value_Planned) and Sum(Valule_Actual) and get the two rows you need.

The problem is really in the Divisions table which is inconsistent with the Data (orders) table. So you should either cleanse and fix the Divisions table during the load script phase by adding the Code for the divisions that doesn't exist in the Data (orders) table. This follows normal Business Intelligence and Data Warehouse best practices - which says that you shouldn't have dimension keys in a fact table that hasn't corresponding keys in the dimension table.

petter
Partner - Champion III
Partner - Champion III

Since I mentioned cleansing the dimension Divisions - here is the load script that could be used to create the missing Code keys:

Divisions:

LOAD *,Code AS Code_ INLINE [ 

Code,Division 

A100,South];


// Make sure to append unknown division codes to the dimension table Divisions:

CONCATENATE LOAD

  Code,

  '(' & Code & ' unknown)' AS Division

RESIDENT

  Data

WHERE

  Not( Exists( Code_ , Code ) ) ;


DROP FIELD Code_;

This is how it will look like:

2018-04-03 18_17_51-#QC 2018-04-02 Problem with values in Table - My new sheet _ App overview - Qlik.png

Notice the (A200 unknown) in the Division column....

mikel_de
Creator
Creator
Author

Hi Petter!

Thanks a lot! Sum(Total <Document> Value) did the trick. It provides the same result as my Aggr() solution but is much faster and does not slow down the app! Exactly what i needed! Btw. I never used this syntax, is it something like set analysis? Could you please tell me how is it called or where I could read more about it?


Also thank you for explaining the reason why my table did not work. So when a dimensional value is missing Qlik will not calculate the measures as I would expect it to. I will not change the script of this particular app, but what you posted as suggestion is very interesting and I would use it in future.


Thank you again!


petter
Partner - Champion III
Partner - Champion III

For the syntax and the documentation of TOTAL <field1,field2....> in any aggregate function you can have a look at what is written about Sum() :

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/ChartFunctions/BasicAggregatio...