So weird: A number divided by one is greater than the number itself ...

[RickyLam]

Hi all,

I have a serious issue of building a pivot table in Qlik Sense.

The pivot table would show the total sales and "average sales" which are to be broken down by quarter of the order date, product description and order ID (in that order). 

and the simplified expression for total sales is "=SUM([OrderDetails.LineTotal])" and for "average sales", it is:

=SUM([OrderDetails.LineTotal] / IF(InQuarter([Order.OrderDate], Today(0), 0), 1, 1))

The seemingly nonsensical denominator "IF(InQuarter([Order.OrderDate], Today(0), 0), 1, 1)" is just a means of debugging the original, more complex expression.  But that simply means no matter whether the order date is in the same quarter as the current date or not, the denominator is always 1.

So one would expect the total sales and average sales of the pivot table would be the same in all dimensions, right? Not so in my case:

In my case, the avg sales of some quarters (Q2 and Q3) are even greater than their respective total sales!!

On drilling down to the OrderID dimension, some interesting patterns emerges:

Only some orders have this issues, e.g those highlighted in yellow in the above screenshot. 
For those orders that do not exhibit the problem, all of them contains only one single item like this:

For those orders which have the problems, they always contain multiple items like the one shown below:

The there is a relationship that explains the apparent discrenpancy: The avg daily sales = Total sales x Total number of items in the order. Here, the order shown above has 3 items. Hence the avg daily sales is apparently computed as:
15160 Total Sales x 3 items = 45480 Avg Sales. 

Further observations (No screen shot here):

1.  If the denominator of the avg sales expression is change to something like that:
IF(InQuarter([Order.OrderDate], Today(0), 0), 1, x) where x is not equal to 1, then the apparent relationship between avg sales and total sales would be:

Avg sales = total sales x total number of items in the order / x

That means the IF function does work, just Qlik Sense insists on multiplying the total number of items of an order also.

2. If the "Source Data Table SDT", shown in the above screen shots, is exported as an Excel worksheet, imported it into a new app, re-create the pivot table above, the avg sales measure would then calculated properly with no issues. So I am sure that there must be something wrong inside the orignal app, perhaps some unknown joins or connections, that cause the problem....

I have attached the qvf file which contains the pivot table and the raw table shown in the screenshots above.  I would appreciate it if you could take some time and have a look on that qvf file and offer me some insights about how to deal with this issues. Many thanks in advance. 

[marcus_sommer]

I think you have an issue with your data-model respectively the associations between the tables because your mentioned wrong values are exactly the twice, thrice, ... from the right value. This hints for any duplication of the data directly or in regard to the associations.

[RickyLam]

Could you elaborate more on this?

In the data model (refer to the attached qvf file), the is one separate table for Orders (PMT_ORDER) and another one for order details (PMT_ORDER_ITEM). And I can see no duplication of values in the raw data table presented in the same sheet of the attached qvf file. 

I would appreciate it very much if you could take a look of the attached avf file and give me some hints about that. 

[marcus_sommer · Accepted solution]

I have no Sense available and couldn't look into the application. There may no direct duplication in the data - whereby without an unique identifier you won't see them. Very helpful is in such cases to add recno() and rowno() within the loads to track each single record from the source to the target.

The best way to check the data would be to use a table-box with the relevant fields including the above mentioned identifier.

[RickyLam]

Sorry for my late response.

Your suggestion that adding row numbers to the original tables does help troubleshoot my case. It turns out that the table does contain duplicate data, in a very obscure manner. Thanks for giving me such a valuable insight.