Hi all - I am relatively new to Qliksense and have been almost entirely self taught, so, I ask in advance for your patience.
I have a data set of workshop attendance full of the usual things - Member ID, who attended, the date of the event, etc. We use this data for scoring company participation across all of our membership, but I am struggling to find the right way to do this so I only count each company once and not based on how many people they had attend (it will make more sense in a moment).
Sample Data Set:
Member ID, Event Date, Attendee, Attend (Y or N)
47
2018-10-21
David
Y
47
2019-05-15
David
Y
50
2018-10-21
Greg
Y
50
2019-05-15
Patrick
Y
51
2018-10-21
Jordan
Y
51
2019-05-15
Phillip
Y
52
2018-10-21
-
N
52
2019-05-15
-
N
54
2019-05-15
Bryan
Y
54
2019-05-15
Danielle
Y
54
2018-10-21
David
Y
54
2019-05-15
David
Y
54
2018-10-21
Reggie
Y
54
2019-05-15
Reggie
Y
55
2018-10-21
-
N
55
2019-05-15
-
N
57
2019-05-15
Austin
Y
57
2018-10-21
-
N
In our scenario, Y = 100 and N = 0 points, by company. If a company sends one person or a thousand, they still get only 100 points.
On companies that send only one person, this works just fine. For companies who send 6 people, I get a value of 600 instead of just the 100.
My formula thusfar (yes I know it's very simple, I'm still learning!)
I have a table set up on our back end of modifiers and values (because the formulas are mostly the same across what we do, just the values change slightly) - W_Value = 100.
SUM(
IF ([Attended Workshop] = 'Y' , [W_Value],
IF ([Attended Workshop]='N',0)))
I've tried various flags in the load script, Counts of Distinct IDs and things of that nature but I just can't quite put my finger on how to make this work. Please help!
