event count in fact table

[ilanbaruch]

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

[pradosh_thakur]

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.

[bharathadde]

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)

[MindaugasBacius]

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

Please check the attached qvw file.

[ilanbaruch]

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

[ilanbaruch]

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

[ilanbaruch]

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

[MindaugasBacius]

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.

[bharathadde]

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.