Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
612118513
New Contributor II

Conditional SUM

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.0010/31/2018
$160,092.89$830.18 
$0.00$0.00 
$8,232.67$0.0010/17/2018
$2,598.87$0.009/4/2018
$635.52$0.0010/30/2018
$5,174.93$0.0010/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?

7 Replies
zzyjordan
Contributor II

Re: Conditional SUM

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

Re: Conditional SUM

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:

2019-01-21_23h51_17.png

612118513
New Contributor II

Re: Conditional SUM

Hi ZZ.

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

BR

Jacek
Contributor

Re: Conditional SUM

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

612118513
New Contributor II

Re: Conditional SUM

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
Contributor II

Re: Conditional SUM

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
Valued Contributor

Re: Conditional SUM

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