Numbers don't add up

[#1]

Dear Qlikview Community,

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

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

[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 · Accepted solution]

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

[#4]

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?

[#5]

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.

[Mark_Little]

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

[#7]

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.

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.