Sum If

[MikeJones]

Hi, the first 5 columns are a snapshot of my data table in Excel.  Coln titled MExp shows the value of MetersInstalled should it be above MetersExp.

Jobid	Date	MetersExp	MetersInstalled	Client	MExp
1	02/01/2023	2	1	YYYY	2
2	22/01/2023	0	2	XXXX	2
3	11/02/2023	1	1	YYYY	1
4	03/03/2023	0	1	YYYY	1
5	23/03/2023	2	1	XXXX	2
6	12/04/2023	0	1	YYYY	1
7	02/05/2023	0	2	XXXX	2

What I wish to do, is sum by client the number of meters expected using MExp - I don't wish to create a new variable in my data variable table.  I have been trying different Set Analysis expressions and now I'm hitting my head against a wall.

[aytacbas]

Hi Mike,

you can use aggregation function like this

Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})

The result you expect are as follows

[MikeJones · Accepted solution]

Thanks Aytacbas for your idea.

What I need to do is

sum(if(MetersInstalled>MetersExp,MetersInstalled,MetersExp), however I'm uncertain how to do this in set analysis.

[Cascader · Accepted solution]

you can ease a bit by using a calculated column

load

jobid,

date,

...,

if(MetersInstalled>MetersExp,MetersInstalled,MetersExp) as x

from qvd;

then use the set analysis the way you like.