Qlik Community

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.

AccCal123
Para
a100100200300300600
b100100200300300600

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

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

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

Result:

AccCal123
Para
a01000300300600
b01000300300600

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

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

AccCal123
Para
a100100200300300600
b100100200300300600

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:

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.


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:

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.


talk is cheap, supply exceeds demand
Not applicable

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

Thank you, Gysbert. Its work!

Community Browser