Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm currently woking with incoming and outgoing transactions, which comes as absolute values. In order to track the daily account balance I've been using the below formula in my graph, which works fine. US = my client.
sum( {<Receiver = {'US'}>} [Amount] )- sum( {<Receiver -= {'US'}>} [Amount] ))
However, now im trying to figure out how to find the maximum and minimum values per day. And then rank these, showing the top three daily Max and Min values during the month.
Pls find attached, any ideas?
Regards,
Olle
Sorry, i cant understand your expression.
You have transactions, some of these is from "US" and the rest is from "no US". Why you just substract those?
Make this exercise to understand better. Supose we have the following data:
Date | Receiver | Amount |
---|---|---|
1/1/2014 | US | 100 |
1/1/2014 | A | 50 |
1/1/2014 | B | 30 |
2/1/2014 | US | 150 |
2/1/2014 | A | 70 |
2/1/2014 | B | 80 |
Tell me the Min and Max values you need to see per day.
YOu can use dimension limits in QV11
Attached
Hi Sebastian,
First, I've rounded all the timestamps to quarters.
Every quarter, for instance 09:15 and 09:30 outgoing payments are being made by US (debeting money from the account) and incoming payments are credited to the account. By summing all the incoming payments and deducting the sum of outgoing payments (Receiver = US) for every quarter you will receive a netted balance for that specific quarter. Full accumulation throughout the day!
Receiver = US means other counterparties are crediting our acc
Receiver <> US means we are paying other counterparties, resulting in a debit.
Now the problem is I need to find the top 3 and bottom 3 daily balances for every month.
In your example (given the transaction order you've stated):
Ranking Max Min Running Balance:
1st Max = 150 (first transaction 2/1)
2nd Max = 100 (first transaction 1/1)
3d Max = 80 (first - second transaction 2/1)
1st Min = 0 (first-second-third transaction for 2/1)
2nd Min = 20 (first-second-third transaction for 1/1)
3rd Min = 50 (first - second transaction for 1/1)
Hopes this makes it clearer
Kind Regards,
olle
Anyone?
Olle, sorry for the delay.
See the attach.
As you send only 3 dates of data, I setted up the graphs to show only 2 max values and 2 min values (if those where 3, you always see the same values)
To show 3, you must correct the value setted in "Dimmention Limits".
Hi Sebastian,
I appreciate your answer, however It does not seem to work properly, since it's accumulating the different max/min figures from the previous date. Also, I need it presented in a table. You can check the table in the upright corner and compare this to your graphs.
I've added a few more dates and calculated the correct top three:s in Excel as per below:
Best Regards,
Olle