topic Re: Total issue using Sum Function in QlikView

Total issue using Sum Function

Charlie007 — Sat, 16 Nov 2024 18:40:03 GMT

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.00	Actual Total	sum(DISTINCT Amount)
740,312.00	Shown in QV
1,500.00	Difference

Re: Total issue using Sum Function

Saravanan_Desingh — Tue, 12 May 2020 00:24:02 GMT

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?

Re: Total issue using Sum Function

Charlie007 — Tue, 12 May 2020 06:14:56 GMT

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

Re: Total issue using Sum Function

Saravanan_Desingh — Wed, 13 May 2020 03:51:12 GMT

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 ];

Re: Total issue using Sum Function

Charlie007 — Wed, 13 May 2020 05:34:42 GMT

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.

Re: Total issue using Sum Function

Saravanan_Desingh — Wed, 13 May 2020 12:45:46 GMT

You can post a sample file to look into.

Re: Total issue using Sum Function

Charlie007 — Thu, 14 May 2020 08:10:42 GMT

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.

Re: Total issue using Sum Function

Charlie007 — Mon, 13 Jul 2020 06:44:16 GMT

	Total Through QlikView	Manual Total in Excel	Not Duplicate entries
A/C Description	Medical Expenditure
MEDICAL BILLS - OFFICER PARENT	608,326.00	608,326.00
MEDICAL BILLS - OFFICER PARENT	179,023.00	179,023.00
MEDICAL BILLS - OFFICER PARENT	26,460.00	26,460.00
MEDICAL BILLS - OFFICER PARENT	14,490.00	14,490.00
MEDICAL BILLS - OFFICER PARENT	8,370.00	8,370.00
MEDICAL BILLS - OFFICER PARENT	6,646.00	6,646.00
MEDICAL BILLS - OFFICER PARENT	6,615.00	6,615.00
MEDICAL BILLS - OFFICER PARENT	6,615.00	6,615.00	6615
MEDICAL BILLS - OFFICER PARENT	6,200.00	6,200.00
MEDICAL BILLS - OFFICER PARENT	6,000.00	6,000.00
MEDICAL BILLS - OFFICER PARENT	5,400.00	5,400.00
MEDICAL BILLS - OFFICER PARENT	3,825.00	3,825.00
MEDICAL BILLS - OFFICER PARENT	3,735.00	3,735.00
MEDICAL BILLS - OFFICER PARENT	3,150.00	3,150.00
MEDICAL BILLS - OFFICER PARENT	3,100.00	3,100.00
MEDICAL BILLS - OFFICER PARENT	2,900.00	2,900.00
MEDICAL BILLS - OFFICER PARENT	2,508.00	2,508.00
MEDICAL BILLS - OFFICER PARENT	2,500.00	2,500.00	2500
MEDICAL BILLS - OFFICER PARENT	2,500.00	2,500.00	2500
MEDICAL BILLS - OFFICER PARENT	2,500.00	2,500.00
MEDICAL BILLS - OFFICER PARENT	2,205.00	2,205.00
MEDICAL BILLS - OFFICER PARENT	1,360.00	1,360.00	1360
MEDICAL BILLS - OFFICER PARENT	1,360.00	1,360.00
MEDICAL BILLS - OFFICER PARENT	1,250.00	1,250.00
MEDICAL BILLS - OFFICER PARENT	850.00	850.00
MEDICAL BILLS - OFFICER PARENT	600.00	600.00
MEDICAL BILLS - OFFICER PARENT	560.00	560.00
MEDICAL BILLS - OFFICER PARENT	528.00	528.00	528
MEDICAL BILLS - OFFICER PARENT	528.00	528.00	528
MEDICAL BILLS - OFFICER PARENT	528.00	528.00
MEDICAL BILLS - OFFICER PARENT	350.00	350.00
MEDICAL BILLS - OFFICER PARENT	300.00	300.00	300
MEDICAL BILLS - OFFICER PARENT	300.00	300.00
Grand Total	897,251.00	911,582.00	14331
	Wrong Total	Correct Total	Difference