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: 
jmtatum2000
Contributor
Contributor

Differing totals when adding a dimension to a straight table

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.

5 Replies
CatherineB
Partner - Contributor
Partner - Contributor

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

jmtatum2000
Contributor
Contributor
Author

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.Qlikview_LI.jpg

Vegar
MVP
MVP

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

jmtatum2000
Contributor
Contributor
Author

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?

Vegar
MVP
MVP

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];