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

[#1]

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

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

[datanibbler]

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]

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

[Anonymous]

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?

[#5]

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

[#6]

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]

Hi!

Sorry for not presentable syntax and look attach.

[#8]

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]

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

[#10]

sorry,

i didn´t get it working 😕