Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
ilanbaruch
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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.