Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Incorrect SUM value

I have a value which is supposed to SUM room minutes, however I cannot get the value to line up with the manual calculation.

Here is my table model:

Tbls.png

Here are the individual values for the room minutes:

Mins.png

The sum of this is 1880

Here is what I get when using the SUM function:

The value represented from this formula is 1970...?

sum.png

Why is the SUM value incorrect?

8 Replies
Highlighted

What you are seeing is the distinct values within Room Minutes field, but there might be multiple values for the same number. Sum([Room Minutes]) will sum all values. Not a good idea, but Sum(DISTINCT [Room Minutes]) will give the sum of distinct values within Room Minutes field. Is that what you want?

Highlighted

May be use this in text box and then see in Straight/Pivot

Sum([Room Minutes])

Or

Sum(TOTAL [Room Minutes])

Or

Sum(Distinct [Room Minutes])

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Anonymous
Not applicable

I think "90" is not part of the selection "HILLCREST MAIN OR"

Highlighted
Creator
Creator

I selected each ID manually and it appears the 90 minute case is being summed twice. Not sure how or why...?

test.png

Highlighted
Anonymous
Not applicable

try Concat([Room Minutes],' | ')


as expression for debugging

Highlighted
Specialist
Specialist

Add Dimensions to the straight table from the tables CALBLOCK and STGII, you will be able to see where the data is duplicating. If still cannot find the values on which the expression is duplicating try adding dimensions from CASES table as well.

Highlighted
MVP
MVP

It seems to me like your issue can be explained only looking at the CASES table, since both the dimension and expression field originate from that table.

What is the primary key of that table? Is it [Case No], i.e. is there only a single record per case no?

Or is it [Log ID]? Or a combination of fields?

Try to add the primary key to your straight table as dimensions.

Another way to investigate could be to check your data source and script. Are you using JOIN in your script related to table CASES? A JOIN may unintentionally duplicate records.

After all, maybe the value shown is indeed correct, so we might need also to discuss the way you've calculated the sum manually. How have you done it? As Sunny pointed out, a list box or table box will only show distinct combinations of values, it will not show duplicate records or sets of identical values.

Highlighted
Contributor
Contributor

Hi,

 

With this debug method i've noticed my formula cut off after 68 seperates; yet the straight table has 75 rows; any clue what the cause is in this situation?