topic Add only one value per row ID in App Development

Add only one value per row ID

qlikwiz123 — Tue, 04 Feb 2025 21:24:47 GMT

I need to do a sum on a Value column per Row ID.

Row ID	Value
0	0.5
0	0.5
0	0.5
1	1.7
1	1.7
1	1.7
1	1.7
2	2.3
2	2.3
3	1.1
3	1.1
3	1.1
3	1.1
3	1.1

My calculation should take only one value per Row ID

0.5+1.7+2.3+1.1 = 5.6

Re: Add only one value per row ID

Lisa_P — Tue, 04 Feb 2025 22:33:05 GMT

If you are doing this in the front end (sheets/charts), you would use Row ID as dimension and sum(Value) as measure.

To do this in the load script you use the group by function to aggregate per Row ID..

Load "Row ID",
Sum(Value) as NewValue
From ...
Group by "Row ID";

This creates a new table with the aggregated values.

Re: Add only one value per row ID

qlikwiz123 — Tue, 04 Feb 2025 23:27:18 GMT

@Lisa_P Hi, I want to do this as a Text KPI on the front end. Built it using

SUM(Aggr(Only(Value),Row ID))

Re: Add only one value per row ID

Dataintellinalytics — Wed, 05 Feb 2025 05:15:04 GMT

@qlikwiz123 IF you are looking for KPI then I believe you have two options

1. Use Sum(Value) formula in KPI and then select any Row Id as per need in filter.

2. Or you can use set analysis and hardcode the Row Id using following Sum ( {<Row ID = {0}>} Value)

Re: Add only one value per row ID

marcus_sommer — Wed, 05 Feb 2025 11:40:40 GMT

Maybe just:

sum(aggr(sum(distinct Value), [Row ID]))
or
sum(aggr(avg(Value), [Row ID]))

Re: Add only one value per row ID

Qrishna — Wed, 05 Feb 2025 12:17:28 GMT

if you are trying to sum up the distinct values and show on the KPI, then = Sum(DISTINCT Value) should work. if try to group by Row ID you may get different results but if you wanna consider different values for the same id and do it for all other ids then prolly @marcus_sommer solution should do the job.