topic Re: issue with Sum(Value) in App Development

issue with Sum(Value)

Raju_6952 — Tue, 09 Jan 2024 09:27:17 GMT

Hi,

I have value field coming from excel file and sum(value) is giving wrong value. any idea why the sum(value) is wrong here. I am using measure like sum(value) not used any set analysis.

Thanks in advance,

Re: issue with Sum(Value)

P_Kale — Tue, 09 Jan 2024 09:40:46 GMT

Hi,

What values do you have in excel. Pl share the file

Re: issue with Sum(Value)

Or — Tue, 09 Jan 2024 09:53:06 GMT

Presumably, what you're showing as "one row" is actually 431 rows of data with the same unique dimension combination. You should be able to confirm this by adding e.g. Count(Value) as a measure.

Re: issue with Sum(Value)

Raju_6952 — Tue, 09 Jan 2024 10:41:14 GMT

Hi @Or ,

Yes count(Value) is 431 only, any idea to make it work .

Re: issue with Sum(Value)

Or — Tue, 09 Jan 2024 10:47:03 GMT

Fix whatever is causing the duplication in the data model, or don't use sum(), or use aggr() in some way to get around whatever is wrong with the data model. Impossible to say without knowing what the data model is like.

Re: issue with Sum(Value)

rwunderlich — Tue, 09 Jan 2024 18:25:24 GMT

Since all 431 rows have the same value for "Value", I assume you could use Sum(Distinct Value).

As @Or suggested, you may want to investigate the duplication in the data model to determine if it's valid.

-Rob

Re: issue with Sum(Value)

Raju_6952 — Wed, 10 Jan 2024 09:18:07 GMT

Hi @rwunderlich ,

Thanks for the update, I used distinct to remove the duplicates.

Regards,

Raju

Re: issue with Sum(Value)

Or — Wed, 10 Jan 2024 12:00:51 GMT

Sum(Distinct) is almost never a good idea. For example, consider that in the below dataset, the sum of A will be 3, the sum of B will be 5, and the total sum distinct will be 6 rather than 8.

Dim1, Value

A, 1

A, 2

B, 2

B, 3

I'd strongly advise either fixing the data model or, if that's not feasible for some reason, working with aggr(). Sum(Distinct) is (almost) never the answer.

Re: issue with Sum(Value)

Raju_6952 — Wed, 10 Jan 2024 12:07:21 GMT

the problem is that on the source side data is loaded twice or thrice with similar data as follows.

need to consider only value for latest modified date for each primary key field.

Re: issue with Sum(Value)

rwunderlich — Wed, 10 Jan 2024 15:31:07 GMT

Then I would suggest something like:

Sum(Aggr(FirstSortedValue(Value, -ModifedDate), PrimaryKey))

Not sure if you will need "NoDistinct" in the Aggr(). Didn't get a chance to test it.

-Rob

Re: issue with Sum(Value)

Raju_6952 — Thu, 11 Jan 2024 07:22:05 GMT

Hi @rwunderlich ,

Thanks for the update but not working as expected.

Regards,

Raju