Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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
Highlighted
Creator II
Creator II

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

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

Highlighted
Contributor II
Contributor II

Hi ZZ.

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

BR

Creator
Creator

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

Highlighted
Contributor II
Contributor II

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.

Highlighted
Creator II
Creator II

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
Highlighted
Specialist II
Specialist II

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