# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

Hello, guys. I need your help.

I have 2 tables

CalAccCal
11
12
22
13
23
33

CalFactParam
1100a
2200a
3300a
1100b
2200b
3300b

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!

1 Solution

Accepted Solutions

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

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

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.

talk is cheap, supply exceeds demand
2 Replies

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

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

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.

talk is cheap, supply exceeds demand
Not applicable

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

Thank you, Gysbert. Its work!