Create KPI which get the average of the count of distinct values within a group by column

[slacayo]

I have a table:

LOAD * INLINE [
     mid, sid,  ordernum
     A,    A1,    -
     A,    A1,    -
     A,    A1,    -
     A,    A2,    -
     A,    A3,   123
     B,    B1,    -
     B,    B2,    -
     B,    B3,   159
     B,    B3,    -
     C,    C1,    -
     C,    C2,    -
     C,    C2,    -
     D,    D1,    -
      .     .     .
      .     .     .
];

For some context, I am working with data from people who traverse through a website. `mid` is their main id, `sid` is their session. A user, lets say `B` can have multiple distinct sessions `B1`,`B2`, etc.  When the session is repeated, it means that they're simply doing an action within a session. If an order took place in a session, It returns an order number.

As you can see, most sessions don't contain an order number. Most importantly, some `mid`s, ie people have never purchased (like C for example). I want to essentially:

Return all `mids` of people who purchased (where `mid` has a non-null, or some numeric number for the `order num`). From that point, I want to count the amount of  distinct `sids` for every `mid` and then average them.  So example below

Return all `mids` with a non-null or numeric value:

So we would get `A` and `B`. We count the distinct `sids` (A1, A2, A3, B1, B2, B3) and then we divide 6 by the total amount of returned `mids` 6/2 

Hope this is clear!

[stevejoyce · Accepted solution]

=count(distinct {<mid = P({<ordernum -= {'-'}>} mid)>} sid)
/
count(distinct {<ordernum -= {'-'} >} mid)

you may need to handle nulls differently if these are true nulls or dashes.

[slacayo]

Can you walk me through how you got to this solution? Where does the `P` come from? Also how would I change this if the dashes are nulls

[stevejoyce]

nulls i would do *={"*"}.

numerator: count(distinct {<mid = P({<ordernum -= {'-'}>} mid)>} sid)

counts sid, and limits mid, to possible mids that have an order num <> "-".  or nulls could be ordernum *= {"*"}

denominator:

count mids that have an order num <> "-".  or nulls could be ordernum *= {"*"}