Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Need help in determining Time Period in which calculation takes place

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)

DateName / DescriptionAccountDeposit / WithdrawalAmount (EUR)
22-1-2015Salary12345678Depositxxxx
21-1-2015Supermarket12345678Withdrawal15

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!

2 Replies
elena888
Valued Contributor

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

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

Not applicable

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

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!