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!
