topic Re: Differing totals when adding a dimension to a straight table in QlikView

Differing totals when adding a dimension to a straight table

jmtatum2000 — Wed, 25 Nov 2020 16:16:04 GMT

I am having some duplication when I add some dimensions to a straight table that I created. The sum of the dollars is different when I add a dimension which is a part of our product hierarchy. Is there a way to fix this duplication in the script?The totals are not matching when you export to excel and sum the rows.

Re: Differing totals when adding a dimension to a straight table

CatherineB — Tue, 09 Apr 2019 22:11:40 GMT

Hi can you post some screenshots of that and what outcome you're expecting?

Re: Differing totals when adding a dimension to a straight table

jmtatum2000 — Wed, 10 Apr 2019 13:19:49 GMT

The numbers that I circled below should match. I used the sum of rows for both to show the differences. The 2018 numbers match exactly and the units for both 2019 and 2018 match. All I did was swap a dimension and the 2019 numbers come out differently in the sum of total rows.

Re: Differing totals when adding a dimension to a straight table

Vegar — Wed, 10 Apr 2019 15:56:06 GMT

Make sure that you have not excluded null values for the dimension in the object properties.

Which value do you get if you remove all dimensions?

An common problem with the sum of rows is that the total is not the global total if you have transactions that are linked to more than one dimension field value.

Consider this:
Load * inline [
Fruit, Color
Apple, Red
Apple, Green
Apple, Yellow
Banana, Yellow
Strawberry, Red];

Load * inline [
Fruit, Quantity
Apple, 100
Strawberry, 200
Banana, 300];

If you have sum of rows you will get two different total values depending on your chart dimensions.
Fruit:
Apple=100
Strawberry=200
Banana=300
SUM OF ROWS=600
REGULAR SUM=600

Color:
Red=300
Green=100
Yellow=400
SUM OF ROWS=800
REGULAR SUM=600

Re: Differing totals when adding a dimension to a straight table

jmtatum2000 — Wed, 10 Apr 2019 17:54:55 GMT

If I remove all the dimensions the 25,395,621 is the total number. That is the correct number and the raw data adds up to that number. Is there a way to prevent the duplication? The data rolls up by UPC and then the hierarchy matches with the UPC. In some cases there is the same UPC with multiple descriptions associated with the same UPC. Is this what could be causing the issue? Is there a way to just load the first matching description to a UPC?

Re: Differing totals when adding a dimension to a straight table

Vegar — Wed, 10 Apr 2019 19:22:40 GMT

Let me explain two approaches based on my simple fruit/color example.

ApplyMap-method.

Create a map table for the UPC-dimension table and use applymap tp get the first value into your transaction table. (I often use this as an alternative to join when I'm not certain about uniqueness of my data)

MapColors:
Mapping Load * inline [
Fruit, Color
Apple, Red
Apple, Green
Apple, Yellow
Banana, Yellow
Strawberry, Red]
;

Transactions:
Load 
	Fruit, 
	Quantity, 
	ApplyMap('MapColors', Fruit) as Color 
inline [
Fruit, Quantity
Apple, 100
Strawberry, 200
Banana, 300]
;

First value-method

If you want to keep your UPC data as an dimension table you can choose to only keep the first instance of a dimension value by using firstValue()

Colors:
Load 
   Fruit, 
   FirstValue(Color) as Color 
inline [
Fruit, Color
Apple, Red
Apple, Green
Apple, Yellow
Banana, Yellow
Strawberry, Red]
group by Fruit;

Transactions:
Load * inline [
Fruit, Quantity
Apple, 100
Strawberry, 200
Banana, 300];