Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Find and Rank Min/Max Balances

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

6 Replies
sebastiandperei
Specialist
Specialist

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:

DateReceiverAmount
1/1/2014US100
1/1/2014A50
1/1/2014B30
2/1/2014US150
2/1/2014A70
2/1/2014B80

Tell me the Min and Max values you need to see per day.

Not applicable
Author

YOu can use dimension limits in QV11

Attached

Not applicable
Author

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

Not applicable
Author

Anyone?

sebastiandperei
Specialist
Specialist

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".

Not applicable
Author

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:

MaxMin.JPG.jpg

Best Regards,

Olle