sum(if(Cal=AccCal,Fact)) without IF.

[#1]

Hello, guys. I need your help.

I have 2 tables

Cal	AccCal
1	1
1	2
2	2
1	3
2	3
3	3

Cal	Fact	Param
1	100	a
2	200	a
3	300	a
1	100	b
2	200	b
3	300	b

I need to calculate the sum (Fact) from both accumulated calendar and simple calendar in the same report.

AccCal	1		2		3
Para
a	100	100	200	300	300	600
b	100	100	200	300	300	600

sum({<Cal = {"=AccCal=max(Cal)"}>} Fact)

sum({<Cal = {"=AccCal=Cal"}>} Fact)

sum({<Cal = {"=AccCal"}>} Fact)

Result:

AccCal	1		2		3
Para
a	0	100	0	300	300	600
b	0	100	0	300	300	600

If I use sum(if(Cal=AccCal,Fact))

than I get the right result!: but with the big data it is impossible.

AccCal	1		2		3
Para
a	100	100	200	300	300	600
b	100	100	200	300	300	600

How can I get the same result by using set expressions?

The sample application in the attachment.

Tnx a lot!

[Gysbert_Wassenaar · Accepted solution]

What you could do is create a flag field in the script:

Load

rowno() as RowID,

Cal,

AccCal,

if(Cal=AccCal, 1, 0) as CalFlag

from ...somewhere...;

You can then use that new field in your set analysis expression: sum({<CalFlag={1}>}Fact). This will perform best.

It's also possible to use rowno() to create a field that unique identifies records and use that in the expression: sum({<RowID = {"=AccCal=Cal"}>} Fact)

See attached example.

[#3]

Thank you, Gysbert. Its work!