topic Re: How Can I fix Partial sum error in QlikView

How Can I fix Partial sum error

thimiran1997 — Tue, 28 Dec 2021 05:04:41 GMT

There is showing the partial sum according to part no but there is an error. How can I fix it. I attached the picture in below.

#pivot #partial_Sum

Re: How Can I fix Partial sum error

tresB — Tue, 28 Dec 2021 06:00:43 GMT

78 seems to be a negative here. Are you using any number formatting? May be the negative number is being displayed as positive too?

Re: How Can I fix Partial sum error

thimiran1997 — Tue, 28 Dec 2021 05:59:25 GMT

All are positive numbers

Re: How Can I fix Partial sum error

_Gerardo_ — Tue, 28 Dec 2021 13:42:34 GMT

I always say "Qlik is nevers wrong".

This happens when there are duplicate values in the data, maybe the whole record of a table, I suggest you add the field "PART_NO" as a List Box and filter the values UA014008 and UA014009 with the option "Show Frequency" and then add all the fields that come from that table in a Table Box so you can compare the amount of different values that appear in the table versus the ones that the List Box counts.

Are you using DISTINCT in some part of the formula? If you are using it, it is possible that for a single row the values are distinct but for group UA0140 within column 91-120 there are distinct values.

Could you please post your formula?

If you need more help you can write me and I will be glad to help you.

Re: How Can I fix Partial sum error

thimiran1997 — Thu, 30 Dec 2021 09:54:42 GMT

It seems there is no duplicate values . I will attach the qvw file. Please check it. month and year in order 10 and 2021 are use to selections.

Re: How Can I fix Partial sum error

_Gerardo_ — Sat, 01 Jan 2022 18:55:47 GMT

Hello again, I found the difference.

I created the field 61-90 mod with this formula (in green color)

Sum( {<Ageing ={"61-90"},TRANSACTION_CODE={'ARRIVAL'}>-<Ageing ={"61-90"},TRANSACTION_CODE={'RETCREDIT','RETWORK','INVSCRAP','SCPCREDIT'}>} Purchase_Qty)

You used that formula to check if the quantity is lower than Stock Qty (< Sum ( [Stock Qty]) . For testing purposes, I'm not considering the previous aging 0-30,41-60 because in both cases the values were 0.

As you can see in blue color, the condition "< Sum ( [Stock Qty])" is TRUE only for PART_NO UA014008 and UA014009 at row level ... but in the SUM for all the values grouped under StyleP UP UA0140 is FALSE, and Qlik is showing in the right place (TOTAL in red color) the value for group calculation.

Depending what do you want as TOTAL value at Row and Column Level ... you could use Sum ( TOTAL <StyleP> [Stock Qty]) for the comparison ... or if you need something more advanced you could also try using Dimensionality() / SecondaryDimensionality() functions to use a different calculation for each Total Dimension Level.

Dimensionality() function shows the number of dimensions in a row ... and using this you can use a different calculation for TOTAL Dimension 1 for example AVERAGE, and TOTAL Dimension 0 for example a SUM.

SecondaryDimensionality() works similar for columns.

The difference in the calculation of the total is found in other columns as well, but it is not as evident as in the 91-120 column, for example, although the total appears in 61-90 as 1,494, the sum of the individual row values is 1,337.

Re: How Can I fix Partial sum error

_Gerardo_ — Wed, 12 Jan 2022 13:28:17 GMT

Hello @thimiran1997 ,

Did the provided response help you?

Re: How Can I fix Partial sum error

thimiran1997 — Tue, 18 Jan 2022 04:12:45 GMT

Hello @_Gerardo_ ,

Sorry for the my late response. I was little bit busy. your clarification is confused me. I want to sum of columns in styleS level. Could you give me a simple solution or example qvw file.

Thank you for the help