Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Charlie007
Contributor
Contributor

Total issue using Sum Function

Figures  
300.00  
300.00  
6,200.00  
528.00  
560.00  
300.00  
6,000.00  
179,023.00  
608,326.00  
-16,006.00  
-51,504.00  
300.00  
6,885.00  
300.00  
300.00  
741,812.00Actual Totalsum(DISTINCT Amount)
740,312.00Shown in QV 
1,500.00Difference 
Labels (1)
7 Replies
saran7de
Master
Master

In a quick look, I see you are using DISTINCT in the SUM. In your data you have 6 x $300, which results into 1 x $300. The difference of $1500 ( 5 x $300) is due to this DISTINCT. May be you should remove DISTINCT.

Does it make sense?

Charlie007
Contributor
Contributor
Author

If I do not use DISTINCT function then I have following total 913514 which is also incorrect.  I need to know that how can I print total 741812 in sum which is correct total

913,514.00
600.00
600.00
6,200.00
528.00
560.00
600.00
6,000.00
358,046.00
608,326.00
-32,012.00
-51,504.00
600.00
13,770.00
600.00
600.00
saran7de
Master
Master

Your original line items are not same with the above post. Please check. If I use the below code, I am getting 741812.

tab1:
LOAD Sum(Figures) As Total;
LOAD * INLINE [
    Figures
    300.00
    300.00
    "6,200.00"
    528.00
    560.00
    300.00
    "6,000.00"
    "179,023.00"
    "608,326.00"
    "-16,006.00"
    "-51,504.00"
    300.00
    "6,885.00"
    300.00
    300.00
];
Charlie007
Contributor
Contributor
Author

The data is 100% correct, the issue is with Sum Function. I do not know why I am not getting the correct total 741812. Please tell me the exact reason that what formula is to be used to get 741812.

saran7de
Master
Master

You can post a sample file to look into.

Charlie007
Contributor
Contributor
Author

I have already sent the sample data from the file, there is nothing wrong with data. The issue is with Sum function that why it changes the total while calculating manually or calculating in Excel I get the  correct total. The figures with amount 300 (5 times) also correct.

Charlie007
Contributor
Contributor
Author

 Total Through QlikViewManual Total in ExcelNot Duplicate entries
A/C DescriptionMedical Expenditure  
MEDICAL BILLS - OFFICER PARENT608,326.00608,326.00 
MEDICAL BILLS - OFFICER PARENT179,023.00179,023.00 
MEDICAL BILLS - OFFICER PARENT26,460.0026,460.00 
MEDICAL BILLS - OFFICER PARENT14,490.0014,490.00 
MEDICAL BILLS - OFFICER PARENT8,370.008,370.00 
MEDICAL BILLS - OFFICER PARENT6,646.006,646.00 
MEDICAL BILLS - OFFICER PARENT6,615.006,615.00 
MEDICAL BILLS - OFFICER PARENT6,615.006,615.006615
MEDICAL BILLS - OFFICER PARENT6,200.006,200.00 
MEDICAL BILLS - OFFICER PARENT6,000.006,000.00 
MEDICAL BILLS - OFFICER PARENT5,400.005,400.00 
MEDICAL BILLS - OFFICER PARENT3,825.003,825.00 
MEDICAL BILLS - OFFICER PARENT3,735.003,735.00 
MEDICAL BILLS - OFFICER PARENT3,150.003,150.00 
MEDICAL BILLS - OFFICER PARENT3,100.003,100.00 
MEDICAL BILLS - OFFICER PARENT2,900.002,900.00 
MEDICAL BILLS - OFFICER PARENT2,508.002,508.00 
MEDICAL BILLS - OFFICER PARENT2,500.002,500.002500
MEDICAL BILLS - OFFICER PARENT2,500.002,500.002500
MEDICAL BILLS - OFFICER PARENT2,500.002,500.00 
MEDICAL BILLS - OFFICER PARENT2,205.002,205.00 
MEDICAL BILLS - OFFICER PARENT1,360.001,360.001360
MEDICAL BILLS - OFFICER PARENT1,360.001,360.00 
MEDICAL BILLS - OFFICER PARENT1,250.001,250.00 
MEDICAL BILLS - OFFICER PARENT850.00850.00 
MEDICAL BILLS - OFFICER PARENT600.00600.00 
MEDICAL BILLS - OFFICER PARENT560.00560.00 
MEDICAL BILLS - OFFICER PARENT528.00528.00528
MEDICAL BILLS - OFFICER PARENT528.00528.00528
MEDICAL BILLS - OFFICER PARENT528.00528.00 
MEDICAL BILLS - OFFICER PARENT350.00350.00 
MEDICAL BILLS - OFFICER PARENT300.00300.00300
MEDICAL BILLS - OFFICER PARENT300.00300.00 
Grand Total897,251.00911,582.0014331
 Wrong TotalCorrect TotalDifference