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

[#1]

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

[vivek_niti · Accepted solution]

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.

[sridhar240784]

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

-Sridhar

[#4]

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;

[#5]

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!