topic Conditional SUM in QlikView

Conditional SUM

612118513 — Sat, 16 Nov 2024 21:33:50 GMT

Hi Everyone.

I want to create a bar chart that shows me the money collected per month, for this i have a table as follows:

OTC (USD)	MRC (USD)	ACTUAL
$41,031.39	$5,829.63
$2,269.91	$2,931.56
$1,924.21	$4,450.26
$31,663.88	$1,262.93
$0.00	$0.00
$11,090.96	$0.00	10/31/2018
$160,092.89	$830.18
$0.00	$0.00
$8,232.67	$0.00	10/17/2018
$2,598.87	$0.00	9/4/2018
$635.52	$0.00	10/30/2018
$5,174.93	$0.00	10/30/2018

The idea is sum the values where the ACTUAL field is not empty, because ACTUAL with a date means the money was collected, i used the following expression but is showing the sum incorrectly:

Sum(Aggr(If(len(ACTUAL)<> 0, Sum([MRC (USD)])+sum([OTC (USD)])),ACTUAL))

Anyone can help me?

Re: Conditional SUM

zzyjordan — Mon, 21 Jan 2019 22:53:50 GMT

Hi,
Try this
SUM({<ACTUAL={"<>isnull(ACTUAL)"}>}([MRC (USD)]+[OTC (USD)]))
and drag your month as dimension.
Hope this helps
ZZ

Re: Conditional SUM

Jacek — Mon, 21 Jan 2019 22:53:52 GMT

You can create MonthActual field as dimension in Load Script and calculation like:

sum(aggr( RangeSum( sum([OTC (USD)]), sum([MRC (USD)])), ACTUAL))

or just simple:

RangeSum( sum([OTC (USD)]), sum([MRC (USD)])) and set up in QlikView properties:

Re: Conditional SUM

612118513 — Mon, 21 Jan 2019 23:00:01 GMT

Hi ZZ.

I tried with your suggestion but is making the sum incorrectly.

Re: Conditional SUM

Jacek — Mon, 21 Jan 2019 23:02:59 GMT

May I ask you to put here sample data / .qvw with this issue?

Re: Conditional SUM

612118513 — Mon, 21 Jan 2019 23:05:21 GMT

Thanks for your answer Jacek.

For my problem both solutions are not working, because i take the Month dimension from another field, not from the ACTUAL.

Re: Conditional SUM

zzyjordan — Mon, 21 Jan 2019 23:10:30 GMT

Hi, BR
Can you tell what's your expected figures with your sample data?
Also, can you include the month dimension in the sample data as well?

ZZ

Re: Conditional SUM

anushree1 — Tue, 22 Jan 2019 11:25:53 GMT

Issue could be because the Table Hosting the Month Field is not Connected to Table Hosting the Actual Field