Conditional SUM

[612118513]

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?

[zzyjordan]

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

[Jacek]

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:

[612118513]

Hi ZZ.

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

BR

[Jacek]

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

[612118513]

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.

[zzyjordan]

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

[anushree1]

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