Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Numbers don't add up

Dear Qlikview Community,

I have the following issue and I can't find out what is happening.

bugreport.png

The tables shows the following columns:

Location, Net Sales, Accumulated net sales.

As you can see, the very last entry with "-" as Location name (i.e. not assigned, or something like that), has quite an high amount of sales (the 9 M). However, it is not included in the total of the sales you can see at the top (the 14 M).

Even if I make a table without this dimension, I only get the 14 M as the total net sales.

What is going on here/what am I missing?

Thanks in advance for your insights,

Best regards,

Hans

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

You could solve it with enabling the hide-option on NULL within the dimension but I would also take a look on the datamodel to check the data behind them.

- Marcus

View solution in original post

6 Replies
sunny_talwar

If this is a straight table, can you try changing the Total Mode to Sum of rows on the expression tab?

marcus_sommer
MVP & Luminary
MVP & Luminary

You could solve it with enabling the hide-option on NULL within the dimension but I would also take a look on the datamodel to check the data behind them.

- Marcus

Not applicable
Author

If I change the total mode to Sum of y-values (which might be what you meant?) then the last number (the 9 M) are accurately considered for the total sum.

This doesn't change the fact however, that if I only calculate

Sum([#Net-Sales]), I still only get the 14 M and not the correct 24 M. Since this is one of the most simple formulas, I guess this has to do with my data model?

Not applicable
Author

Enabling this option makes the table look correct, which is nice. But the fact that there are values that are otherwise not accounted for confuses me, since simply Sum([#Net-Sales]) also yields only 14M instead of 24M. I guess I have to check the data model then.

mark6505
Partner - Master
Partner - Master

Hi,

Either the options above should address the issue.

Another option is to look at assigning the null a value on load?

Either an applymap or Nullasvalue.

Mark

Not applicable
Author

Thanks to all the answers, I figured that it is not a QV frontend peculiarity causing this behavior and started to dig deeper by looking at the database this data is from. Turns out that on the database everything looks fine. Therefore I figured that the QV backend must me responsible for some funky stuff.

After some testing, I realized the following:

On the database, there are, for whatever reason, locations encoded 001, 002, 003, etc., but are not named. I.e. they have a key but no string for their name. The actual locations used in the day to day business are numbered 1, 2, 3.

bugreport2.png

The two left-most column is the PK of the table, and is basically a copy of the LocationID in the second column. The third column is a concatenation of the LocationID and the LocationName, e.g 99-SydneyWest.

As is evident, the locations numbered 1 through 4 did get duplicated somehow, due to the fact that there were empty location entries with 001, 002, 003, and 004 as their PK and ID. The locations that are actually used are numbered 1,2,3, but have been duplicated and joined(?) with these dummy locations, resulting in duplicated values. Still, in the QV frontend they count as NULL values, and our quick fix is to simply turn off their display in tables.