Current Minus Previous Loaded Date Balance

Tue, 14 Apr 2015 14:49:19 GMT

Good morning,

Is there a way that I can create a variable to calculate the current balance for the previous loaded date? I have data for accounts for all business days and I what I want to do is calculate current balance - previous balance to get the variance. I created a variable to give me the max date and created one for the previous date.

vMaxDate = Max(Date) - for current balance

vPrvDate = Date(Max(Date) - 1) - for previous balance

The problem I am getting is for example on a Monday, the vPrvDate is giving me the day of Sunday and since I only have data for business days, the value that is giving me is 0. What I need for the vPrvDate variable is to yield the last date of loaded data, for example on friday.

Account	Balance	Date
XXXX1	50000	04/09/15
XXXX1	100000	04/10/15
XXXX1	150000	04/13/15
XXXX1	50000	04/15/15

What I need is that when I select Date 04/13/15:

It provides me with the Current Balance of $150,000 and previous balance of $100,000. Causing a positive difference of $50,000

Currenly with the vPrvDate as I have it, when I select Date 04/13/15

It provides me with the Current Balance of $150,000, previous balance of $0 from 04/12/15 (since I dont have data for that date) and a difference of $0.

Please let me know who I can fix this. Thank you.

topic Current Minus Previous Loaded Date Balance in QlikView

Current Minus Previous Loaded Date Balance