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

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

Wed, 14 Oct 2015 07:40:55 GMT

Hello,

i hope somebody can help me with this problem.

I have a Table like:

month	userId	Event
Jan	1	1
Mar	1	1
Jan	2	1
Apr	3	1

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

	Jan	Feb	Mar	Apr
Count (distinct userIds with at least one Event in following three Months	2	2	2	1

Jan

Feb

Mar

Apr

Count (distinct userIds with at least

one Event in following three Months

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

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

datanibbler — Wed, 14 Oct 2015 07:52:26 GMT

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.

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

pokassov — Wed, 14 Oct 2015 07:56:41 GMT

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

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

Anonymous — Wed, 14 Oct 2015 08:02:34 GMT

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?

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

Wed, 14 Oct 2015 08:26:58 GMT

Yes, my raw data looks like:

Date	userId	Event
01.01.2014	1	1
01.01.2014	2	1
...	...	...

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

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

Wed, 14 Oct 2015 09:17:35 GMT

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

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

pokassov — Wed, 14 Oct 2015 10:02:25 GMT

Hi!

Sorry for not presentable syntax and look attach.

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

Wed, 14 Oct 2015 12:04:20 GMT

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

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

pokassov — Wed, 14 Oct 2015 12:23:25 GMT

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

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

Fri, 16 Oct 2015 14:33:17 GMT

sorry,

i didn´t get it working 😕