Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
ilanbaruch
Contributor III

event count in fact table

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_iddatetrans_id
139320170815102
139320170813117
139320170813102
139320170815101
139320170823101
139320170830101
13932017081729
13932017081729
13932017082029
13932017082429
13932017083029
1393201708178
1393201708178
1393201708178
1393201708178
1393201708178
1393201708178
1393201708308
1393201708316
1393201708175
1393201708203
1393201708172
1393201708202
1393201708242
Labels (3)
7 Replies
pradosh_thakur
Honored Contributor II

Re: event count in fact table

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.
Learning never stops.
bharathadde
Contributor

Re: event count in fact table

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
Valued Contributor II

Re: event count in fact table

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

Screenshot_1.jpg

Please check the attached qvw file.

 

ilanbaruch
Contributor III

Re: event count in fact table

my apologize below the relevant table and description

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

trans_iduser_iddateevent_type
100139320170815102
101139320170813117
102139320170813102
103139320170815101
104139320170823101
105139320170830101
10613932017081729
10713932017081729
10813932017082029
10913932017082429
11013932017083029
1111393201708178
1121393201708178
1131393201708178
1141393201708178
1151393201708178
1161393201708178
1171393201708308
1181393201708316
1191393201708175
1201393201708203
1211393201708172
1221393201708202
1231393201708242
ilanbaruch
Contributor III

Re: event count in fact table

my apologize, below the relevant table and description

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

trans_iduser_iddateevent_type
100139320170815102
101139320170813117
102139320170813102
103139320170815101
104139320170823101
105139320170830101
10613932017081729
10713932017081729
10813932017082029
10913932017082429
11013932017083029
1111393201708178
1121393201708178
1131393201708178
1141393201708178
1151393201708178
1161393201708178
1171393201708308
1181393201708316
1191393201708175
1201393201708203
1211393201708172
1221393201708202
1231393201708242
 
MindaugasBacius
Valued Contributor II

Re: event count in fact table

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
Contributor

Re: event count in fact table

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.