Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Sera
New Contributor III

Total Value changes with displayed dimensions

Hallo everyone, 

the total amount in my (straight) table is changing, when i'm adding/removing a specific dimension (see the screenshots below/attached, where i have used the "Show column if"-function to simulate the same thing):

There's no need for actively selected filters. The Formula for Measure "Lagerbestand (01)" is:
= sum({<Firma = {'01'}, LBVKL_test = {'J'}, [VMI Kunde] = {'J'}>}[Menge Bestand])

Further information: 
- all (relevant internal) tables are connected with each other in the data model (and i checked it like 100 times 🤐 )
- Totals function for the measure is set as "Sum" (not auto)
- QlikSense Server Version: 13.21.9   (April 2019 patch 2)
- The correct value should be around 417.000 (so the greater value)
- Converting the straight table to pivot-table, always show the smaller (most likely wrong) of the 2 values... 

Does anyone have an explanation for this strange behaviour and an idea how to fix it? I need it as straight table.

1 without the column.jpgwithout the column2 with the column.jpgwith the column

3 as pivot (with and without the column).jpgAs pivot (with and without the column)

5 Replies
jerifortune
Contributor III

Re: Total Value changes with displayed dimensions

Have you checked your data model? It would be nice if the sample qvf file is shared here.

Partner
Partner

Re: Total Value changes with displayed dimensions

Hi,

maybe is it unable "include null value" for the other dimensions?

Sera
New Contributor III

Re: Total Value changes with displayed dimensions

I already checked the data-model countless times and hours already... 

 

can't provide a QVF with data (since there is customer data in) and i don't know how much it help without, but here's a sample QVF without the data (and reduced to the 2 straight tables + 2 test-wise pivot-tables)

Sera
New Contributor III

Re: Total Value changes with displayed dimensions

i checked every measure, every field, every table, just everything like 5 times to be extra sure about including/excluding null-values (in sight of: the tick-symbol is set in all settings)

Highlighted
Sera
New Contributor III

Re: Total Value changes with displayed dimensions

++ UPDATE ++

We have tested pretty much EVERYTHING now. In hope that someone can solve the problem, i wanna share our observations: 

  • We have made 4 tables: 2 Pivot, 2 Straight.
  • All contain the same dimensions and the same (single) measure
  • In datamodel, everything is connected with each other (no data islands)
  • In 1 Pivot & 1 Straight table, i don't show the field/dimension "Kundengruppe" anymore (set "show column if" to 0)
  • Totals function of the measure is set to "Sum"

The one full-info straight table (with showing "Kundengruppe") shows a bigger number. 
The remaining 3 (straight table & the 2 pivot) tables show a smaller number. 

  • including/excluding of null values and/or zero values didn't change the total number at all
  • changing the Totals function of the bigger-number straight table  to "Auto" changes the number to the smaller amount (the same, like in the other 3 tables)
  • changing the Totals function in any other table doesn't do anything

 

Currently, we think the bigger number is the correct one, but i need the same total value to be shown for ALL levels of detail. Different people need different views, but these total numbers shouldn't depend on the amount of displayed dimensions...