topic Re: Calculating active status in QlikView

Calculating active status

Tue, 11 Nov 2014 20:29:00 GMT

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.

Re: Calculating active status

JonnyPoole — Wed, 12 Nov 2014 01:45:50 GMT

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.

Re: Calculating active status

Wed, 12 Nov 2014 10:01:11 GMT

Thanks for this but is there any way I can do this in the load script rather than in the charts?

Re: Calculating active status

JonnyPoole — Thu, 13 Nov 2014 19:18:17 GMT

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