Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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:
Hi ZZ.
I tried with your suggestion but is making the sum incorrectly.
BR
May I ask you to put here sample data / .qvw with this issue?
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.