topic Re: Calculate the sum without repetitions in QlikView

Calculate the sum without repetitions

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

Hello,

I have the following table

I need to have the sum of MONTANT_TIT without repetitions.

Knowing that the NUMDOM can only have 1 MONTANT_TIT.

Thank you

Re: Calculate the sum without repetitions

Anil_Babu_Samineni — Thu, 18 Jan 2018 14:25:13 GMT

Not sure when you say reputation?

Re: Calculate the sum without repetitions

sunny_talwar — Thu, 18 Jan 2018 14:28:15 GMT

Try this

Sum(Aggr(MONTANT_TIT, NUMDOM))

Sum(Aggr(Avg(MONTANT_TIT), NUMDOM))

Sum(Aggr(Sum(DISTINCT MONTANT_TIT), NUMDOM))

Re: Calculate the sum without repetitions

vishsaggi — Thu, 18 Jan 2018 14:34:01 GMT

WHere are you displaying this data? I mean which object you are displaying this data in Qlikview. Usually QV will take care of the duplicates automatically unless you have any other dimension which is not duplicate and linked to this data.

Re: Calculate the sum without repetitions

jonvitale — Thu, 18 Jan 2018 14:45:31 GMT

Yasmine,

You can certainly use Sunny's Aggr commands, but is there a reason why you need the extra rows in the table? Do each of those rows contain some extra information that we are not seeing? If not, you should consider revising the load script so that the table only has unique rows. Try using "Load Distinct" in the load script.

Jonathan

Re: Calculate the sum without repetitions

Anonymous — Thu, 18 Jan 2018 14:50:20 GMT

there are not duplicated because the date is important for my model, except in this situation i'm not using it.

Re: Calculate the sum without repetitions

Anonymous — Thu, 18 Jan 2018 14:52:41 GMT

Thank you stalwar1‌ I got the correct result .

Re: Calculate the sum without repetitions

jonvitale — Thu, 18 Jan 2018 15:14:28 GMT

Just my 2 cents but,

If I were you I'd probably create a separate table that is an aggregation of this table. It seems to me, that with the Qlik associative engine, you really don't need a table with lot's of repeated information. Create a table with one row per NumDot.

But, hey, that's just my opinion, man.