topic Re: Wrong data when using sum() in QlikView

Wrong data when using sum()

absekrafi — Wed, 25 Nov 2020 16:16:04 GMT

Dear community,

I have a table containing a STOCK field.

I added a text object to debug my application:

=Only({<TRANSACTION_DATE = {'$(vStartDate)'}>} If('$(vStartDate)' <= '$(vEndDate)', STOCK, 0))

>> Gives me the correct value.

=Sum({<TRANSACTION_DATE = {'$(vStartDate)'}>} If('$(vStartDate)' <= '$(vEndDate)', STOCK_CONS, 0))

>> Gives a wrong value.

Could please explain to me the difference between the 2 expressions above.

and what is the meaning of the function Only?

Best regards,

Abdallah

Re: Wrong data when using sum()

sunny_talwar — Wed, 07 Nov 2018 14:56:48 GMT

What is the difference between STOCK and STOCK_CONS field? I mean a sum on 1 field will give a different result from Sum on another field (not even bringing Sum vs Only into discussion here)...

Re: Wrong data when using sum()

absekrafi — Wed, 07 Nov 2018 15:19:04 GMT

That's a mistyping only.

=Only({<TRANSACTION_DATE = {'$(vStartDate)'}>} If('$(vStartDate)' <= '$(vEndDate)', STOCK, 0))

>> Gives me the correct value. 85 798.593

=Sum({<TRANSACTION_DATE = {'$(vStartDate)'}>} If('$(vStartDate)' <= '$(vEndDate)', STOCK, 0))

>> Gives a wrong value. 343 194.372

The value is unique, in the source table, for the example I took (sulfur). So the Sum() function is somehow adding or multiplying the data (from the table).

But, after reading in the qlikview help online, Only() is not a solution to what I want because there are cases where a Sum(): (adding values) is needed.

Can it, also, due to the date format?

Thank you

Re: Wrong data when using sum()

sunny_talwar — Wed, 07 Nov 2018 15:32:26 GMT

You seem to have 4 rows of data with the same value... which is why you are seeing 343,194,372 when you use Sum... If you do Sum(DISTINCT... you will get to your 85,798,593 number...

=Sum(DISTINCT {<TRANSACTION_DATE = {'$(vStartDate)'}>} If('$(vStartDate)' <= '$(vEndDate)', STOCK, 0))

I don't recommend using Sum(DISTINCT as it will DISTINCT in cases where you have a same value which you do want to Sum... but in this case Only and Sum(DISTINCT will give the same value.

Only works because all 4 rows have the same exact value... which is 85,798,593... if a single value was different... for example one row was 5 instead of 85,798,593... you will see 0 using Only function.

Re: Wrong data when using sum()

chrismarlow — Wed, 07 Nov 2018 15:38:28 GMT

Hi Abdallah,

343194.372 is exactly 4 times 85798.593, so I think your Sum is adding this number up 4 times (somehow), your Only is returning the lone value as the 4 values under consideration are all the same (if they were not would return null). Are you absolutely sure the value 85798.593 is unique in your source tables and what happens if you add the TRANSACTION_DATE to a straight table with the 2 expressions?

Regards,

Chris.

Re: Wrong data when using sum()

absekrafi — Wed, 07 Nov 2018 16:02:27 GMT

Hi Sunny,

Adding distinct fixed the problem. I just have one question, to what DISTINCT apply here: to the dates (TRANSACTION_DATE , vStartDate and vEndDate) or to the field STOCK only.

As you mentionned, the problem where we have the same value repeated and we want to add them still has no solution.

Hi Chris,

I enclosed both data from the oracle data base "sum() issue.csv" and the straight table as detailed.

One thing came to my head is when I do a JOIN between the table to get the FACT table, may be I did that more than once with the table containing my data. But, I think I checked that and it's ok. I will checked that again.

Thank you very much,

Abdallah

Re: Wrong data when using sum()

sunny_talwar — Wed, 07 Nov 2018 16:14:47 GMT

DISTINCT is applied to your dimension. So, across all the rows against the dimension... it will pick each of the distinct value

Re: Wrong data when using sum()

sunny_talwar — Wed, 07 Nov 2018 16:15:40 GMT

Join is def. the problem. I suggest to keep the aggregated data in a separate table linked with your FACT Table on a key field to avoid duplication.

Re: Wrong data when using sum()

absekrafi — Wed, 07 Nov 2018 16:36:01 GMT

Sunny,

I removed my "consumption" table from the fact table and at a first sight my data are ok using sum() without DISTINCT.

I still have a synthetic key (because I have 4 common fields: columns between the two tables.)

Thank you so much Sunny and Chris for your time.

Note :I will clean my discussion when my connection to the server is better and change theme to resolved.

Have a great day,

Abdallah

Re: Wrong data when using sum()

sunny_talwar — Wed, 07 Nov 2018 16:48:25 GMT

You can create a new key by concatenating or using Hash functions to create a combined key out of the 4 field. This will then help you remove your synthetic key.

Re: Wrong data when using sum()

absekrafi — Thu, 08 Nov 2018 11:11:58 GMT

Hi Chris,

What if the results are good in a straight table and not in a pivot table with the same formula?

Best regards,

Abdallah

Re: Wrong data when using sum()

chrismarlow — Thu, 08 Nov 2018 11:22:12 GMT

Hi Abdallah,

I would be a bit surprised, If you add fast change option (on general tab) & switch between the two types (pivot & straight tables) does that show any discrepancy?

Regards,

Chris.

Re: Wrong data when using sum()

absekrafi — Wed, 14 Nov 2018 07:45:57 GMT

Hi Sunny,

I added a new field to each table when I use JOIN. it is "recno()". This adds a record number to every entry of each table when doing a JOIN so that the rows will be different.

The problem is fixed.

Note: I like the new community site!

Regards,

Abdallah