topic Re: Count User only if NOT active in last month, but active this month? in QlikView

Count User only if NOT active in last month, but active this month?

Mon, 29 Jun 2015 09:24:28 GMT

Hi all,

I've got a table like this:

User	month	Active
1	Jan	no
1	Feb	yes
1	Mar	yes
1	Apr	no
1	May	yes

Now I want to count the users that were active over month/week/day (cyclic group), but they should only be counted for a month if they were NOT active last month and were active in the concerning month.

So User 1 should be counted only in Feb and in May, but not in any other month.

Would be awesome if somebody could help me!

Thanks a lot!

Best Regards,
Anita

Re: Count User only if NOT active in last month, but active this month?

vivek_niti — Mon, 29 Jun 2015 09:39:41 GMT

Hi,

Doing this at the front end will be tricky.

But you can create a flag at the script level and then count where the flag is 1.

See the attached example.

Re: Count User only if NOT active in last month, but active this month?

sridhar240784 — Mon, 29 Jun 2015 09:50:40 GMT

You could try getting this results from back end using Peek() function.

-Sridhar

Re: Count User only if NOT active in last month, but active this month?

Mon, 29 Jun 2015 12:04:18 GMT

Hi Anita

U can go with this script Using Previous

LOAD User,
     Date#(month,'MMM') as Mon,
     Date(   Date#(month,'MMM'),'MM') as no,
     Active
FROM
[qlikview test file.xlsx]
(ooxml, embedded labels, table is Sheet4);
NoConcatenate
T2:
LOAD if(Active='yes',if(Previous(Active)='no',1,0)) as YesCount,*;
LOAD * Resident T1
Order by User,no;
drop Table T1;

Re: Count User only if NOT active in last month, but active this month?

Tue, 30 Jun 2015 16:02:21 GMT

Hi,

first of all, thank you, your solution in general works fine, but now I have the problem that I have several rows with the same month for one user in my table. Is there the possibility to peek into the row where Date < Peek(Date)? So I want to peek in the last date/month that is different and not in the last loaded row.

Or do you have any other suggestions for solving my problem?

Thanks again!