topic Re: Need help in determining Time Period in which calculation takes place in QlikView

Need help in determining Time Period in which calculation takes place

Sun, 25 Jan 2015 11:22:16 GMT

Hi Guys, just finished designer training and since practice makes perfect, I'm currently trying to build a reporting app for my own personal finances.

Anyway. I would like to have a monitor on my monthly expenses and income. Problem is; I would like to calculate from the moment my salaray is deposited until the next time it is received. And this is unfortunately not a fixed date.

For example; my sheet looks a bit like this (very simplified)

Date	Name / Description	Account	Deposit / Withdrawal	Amount (EUR)
22-1-2015	Salary	12345678	Deposit	xxxx
21-1-2015	Supermarket	12345678	Withdrawal	15

I have made variables for calculating only deposit or withdrawal; no problem. But now I wan't to throw time in the equation.

I would like for it to calculate the Sum of the Deposit or Withdrawal only in the time period starting from the last time 'Salary' can be found in the sheet. So as soon new salary is received it will start calculating again.

Is this possible? If you need any more information or look at my current set analysis I'll be happy to give it.

Thanks in advance!

Re: Need help in determining Time Period in which calculation takes place

Anonymous — Sun, 25 Jan 2015 19:16:43 GMT

Hello,

if I understand the question, you can create a variable for the last date of Salary:

=DATE(Max({<[Name/Description]={'Salary'}>} Date))

then you can use the following expression:

=sum({<[Deposit/Withdrawal]={'Withdrawal'}, Date={">$(vMaxDate)"}>}Amount)

that sum amount of all Withdrawal from the last date of Salary.

Let me know if I have understood.

Thanks,

Elena

Re: Need help in determining Time Period in which calculation takes place

Mon, 26 Jan 2015 12:59:44 GMT

Hi Elena,

Thank you for you help! It wasn't the perfect answer, since the variable strings returned the maximum date after Salary instead of date of salary, but it helped me greatly along the way!

I've fixed it like this:

Variable:

vSalaryDate =MaxString(if([Name / Description] = 'Salary', [Date]))
vEndDate =Date(Today())

And then using it in an expression:

Expression: =sum({$<Date={">=$(vSalaryDate)<=$(vEndDate)"}>} [Amount (EUR)])

Now just to add the Withdrawal/Deposit qualifier and I'm done.

Thank you!