Wrong data when using sum()

[absekrafi]

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

[sunny_talwar]

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)...

[absekrafi]

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

[sunny_talwar · Accepted solution]

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.

[chrismarlow]

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.

[absekrafi]

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

[sunny_talwar]

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

[sunny_talwar]

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.

[absekrafi]

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

[sunny_talwar]

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.