Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Count if id has at least one entry in following three Months

Hello,

i hope somebody can help me with this problem.

I have a Table like:

monthuserIdEvent
Jan11
Mar11
Jan21
Apr31

What i´m trying to achieve is a Table like:

JanFebMarApr

Count (distinct userIds with at least

one Event in following three Months

2221

The logic is, count every user in the given month, if he has a event in the follwing three months.

like Jan, then look in Jan, Feb and Mar if there is an Event.

e.g.:  output for userid 1 is:

-count him in Jan, because he has at least one event in Jan or Feb or Mar

-count him in Feb, because he has at least one event in Feb or Mar, or Apr

-count hin in Mar, because he has at least one event in Mar or Apr, May

-NOT count him in Apr, because he has NOT at least one event in Apr or May or Jun

Hope somebody can help me.

Thanks

Steve

9 Replies
datanibbler
Esteemed Contributor

Re: Count if id has at least one entry in following three Months

Hi steve,

have a look at the PEEK() function in QlikView. Once you have sorted the data in a table - desc. by (userID and) month in this instance, I guess - it allows you to "look" from one record you are currently loading to the last or the one before_last or ...

You cannot of course peek into the records you have not yet loaded which is why you have to sort desc. by month.

=> Then you can build a corresp. IF_function.

HTH

Best regards,

DataNibbler

P.S.: Bear in mind that, in order to use the ORDER BY clause (to sort), you need a RESIDENT LOAD as this is not possible in a "real" LOAD.

pokassov
Valued Contributor

Re: Count if id has at least one entry in following three Months

Hi!

I think you cant solve this using set analysis.

So you have to use scripting.

I would try this idea:

1. you have a table "fact"

2. left join (fact) load ... month-1 as month, 1 as FlagActive2 resident fact

3. left join (fact) load ... month-2 as month, 1 as FlagActive3 resident fact where isnull(FlagActive2)

so you will have for each user in the each month 2 fields FlagActive2 and FlagActive3.

if(rangesum(FlagActive2,FlagActive3)>0,1,0)             <--- this your users.

I hope you have not only month but date to. in case that month has cycle values.

However the main idea is written above.

Best regards

Sergey

balrajahlawat
Esteemed Contributor

Re: Count if id has at least one entry in following three Months

Steve,

We can do it into Pivot table as slicing/dicing feature is their in Pivot table.

Take USerID, Month as dimension and count of event as a measure...

See, if you can share some sample data?

Not applicable

Re: Count if id has at least one entry in following three Months

Yes, my raw data looks like:

DateuserIdEvent
01.01.201411
01.01.201421
.........

this data is connected to Calendar Table by the Date field.

So for aggregated Month, User can have more Entries but in my case User should be count only once per Month

Not applicable

Re: Count if id has at least one entry in following three Months

Hey Sergey,

i tried your solution, but i couldn´t figure it out how to use it right.

could you please give me some sample data?

Thanks

Steve

pokassov
Valued Contributor

Re: Count if id has at least one entry in following three Months

Hi!

Sorry for not presentable syntax and look attach.

Not applicable

Re: Count if id has at least one entry in following three Months

Thank you for your reply,

i thin it´s getting in the right way, but there are still some problems.

with your solution i only get an entry when there is an entry in given month for a user.

so when user1 has only entries in Jan, Feb and Oct

The Flag "FlagActive" is only set in this rows.

So with your solution user1 has Flag "FlagActive" in Jan, Feb, Oct.

But result should be: user1 has Flag "FlagActive" in Jan, Feb, Aug, Sep, Oct.

Because he had an Event in Oct, there should also Flags for Aug, Sep

pokassov
Valued Contributor

Re: Count if id has at least one entry in following three Months

Well, I don't see this requirement in the first post...

From the new point of view we can do something else:

1. Create a table with userID, MonthName for SignDate, and MonthName for ActivityDate with User's activity.

2. concatenate twice the same data except MonthName - use the same approach with AddMonth(..-1) and where Month Name of Sign Date<=Add Month(..-1)

this table represent your expectation

Not applicable

Re: Count if id has at least one entry in following three Months

sorry,

i didn´t get it working 😕