topic Re: event count in fact table in QlikView

event count in fact table

ilanbaruch — Sat, 16 Nov 2024 04:29:25 GMT

hi all

in my model i have a fact table (below) with users dates and events

i want to count user that have several events, i.e :

users that have event - 2 and 102

tried it with :

Count({$<trans_id={'2','102'} > } distinct user_id)

didn't work.... any ideas

user_id	date	trans_id
1393	20170815	102
1393	20170813	117
1393	20170813	102
1393	20170815	101
1393	20170823	101
1393	20170830	101
1393	20170817	29
1393	20170817	29
1393	20170820	29
1393	20170824	29
1393	20170830	29
1393	20170817	8
1393	20170817	8
1393	20170817	8
1393	20170817	8
1393	20170817	8
1393	20170817	8
1393	20170830	8
1393	20170831	6
1393	20170817	5
1393	20170820	3
1393	20170817	2
1393	20170820	2
1393	20170824	2

Re: event count in fact table

pradosh_thakur — Mon, 11 Feb 2019 16:59:22 GMT

What is your exact requirement ? When you say several events is it any event or just 2 and 102. Please tell your expected output from your sample data to help you easily.

Re: event count in fact table

bharathadde — Mon, 11 Feb 2019 20:50:09 GMT

Try user_id instead of distinct user_id since the user_id is same for all trans_id.

Count({$<trans_id={'2','102'} > } user_id)

Re: event count in fact table

MindaugasBacius — Tue, 12 Feb 2019 05:32:36 GMT

With your provided data and the conditions Distinct count works as expected.

Please check the attached qvw file.

Re: event count in fact table

ilanbaruch — Thu, 14 Feb 2019 14:43:31 GMT

my apologize below the relevant table and description

count of users that had in this period , events 2 and 102

trans_id	user_id	date	event_type
100	1393	20170815	102
101	1393	20170813	117
102	1393	20170813	102
103	1393	20170815	101
104	1393	20170823	101
105	1393	20170830	101
106	1393	20170817	29
107	1393	20170817	29
108	1393	20170820	29
109	1393	20170824	29
110	1393	20170830	29
111	1393	20170817	8
112	1393	20170817	8
113	1393	20170817	8
114	1393	20170817	8
115	1393	20170817	8
116	1393	20170817	8
117	1393	20170830	8
118	1393	20170831	6
119	1393	20170817	5
120	1393	20170820	3
121	1393	20170817	2
122	1393	20170820	2
123	1393	20170824	2

Re: event count in fact table

ilanbaruch — Thu, 14 Feb 2019 14:44:12 GMT

my apologize, below the relevant table and description

count of users that had in this period , events 2 and 102

trans_id	user_id	date	event_type
100	1393	20170815	102
101	1393	20170813	117
102	1393	20170813	102
103	1393	20170815	101
104	1393	20170823	101
105	1393	20170830	101
106	1393	20170817	29
107	1393	20170817	29
108	1393	20170820	29
109	1393	20170824	29
110	1393	20170830	29
111	1393	20170817	8
112	1393	20170817	8
113	1393	20170817	8
114	1393	20170817	8
115	1393	20170817	8
116	1393	20170817	8
117	1393	20170830	8
118	1393	20170831	6
119	1393	20170817	5
120	1393	20170820	3
121	1393	20170817	2
122	1393	20170820	2
123	1393	20170824	2

Re: event count in fact table

MindaugasBacius — Fri, 15 Feb 2019 07:23:25 GMT

What is wrong with the above mentioned solutions?
Count({$<trans_id={2, 102} > } Distinct user_id) or
Count({$<trans_id={2, 102} > } user_id)

Add those to Expression tab in Pivot table and add date field as the Dimension.

Re: event count in fact table

bharathadde — Fri, 15 Feb 2019 19:56:39 GMT

Are you trying to calculate count of user_id or count of trans_id

if count of user_id

Count({$<event_type={2, 102} > } user_id)

if count of trans_id

Count({$<event_type={2, 102} > } trans_id)

Be specific if you want to show this in just a text box or do you want it to use on chart.