Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I am a qlikview newbie so I am slightly stumped by this problem. I need to calculate the 'Active' status of accounts at the end of each month where active is defined as having one or more transaction in the last 3 months
The data I have has
Account Number,
Transaction Month (e.g. '01/10/2014' for Oct-14)
Number of credit transactions in Transaction Month
Number of debit transactions in Transaction Month
I want to calculate a flag such that if sum of credit and debit transactions over the last 3 Transactions months >0 then Active flag is 1, else 0.
Thus the flag at the end of say Jun-14 will be a sum of transactions from Apr-14 to Jun-14 and so on for each month
The output will be
Account Number
Transaction Month
Active flag
So how can I do it? It is the aggregation over previous two months that's stumping me.
Thanks for your help in advance.
It would make sense that this chart have a single time context. Say, todays date or perhaps the most recent date in the data set or even the most recent date in the active user's selection filters.
Will that be the case ?
IF yes, Then it will be a relatively straight forward SET ANALSYIS statement/ If its today's date, then the SET statmenet would to pick up a Date greater/equal than todays date 3 months ago and less than/equal to todays date
sum(aggr( if ( sum ( {$<Date={">=$(= Date(addmonths(Today(),-3))<=Date(Today())"}>} [Credit Transactions] + [Debit Tranactions]) > 0 , 1) , Account))
Also might need an aggr() statement to sum up the results of the 3 month subtotal per account (sum of sums).
If you can prep /share a data set that would help test / share a working example.
Hi
Thanks for this but is there any way I can do this in the load script rather than in the charts?
Arbit - short answer is yes, but it would take a bit of time (not too long) to develop the script to do it. I may get time shortly , but just wanted to respond with the short answer...