4 Replies Latest reply: May 13, 2016 8:10 AM by Marcus Sommer RSS

    Indirect set analysis, nr of users in a month

    Michiel van de Goor

      I want to calculate the number of users that declared hours.

       

      Hour data is in column:                    NROFHOURS

      The unique employee is in column: %FK_USER    which links to the User dimension (from which I use USER_NUMBER >>>)

      The data is stored on daily basis

       

      I want to devide the (rangesum) revenue by the actual number of users in each month.

       

      I have a master calendar table with Dimension Year-Month.

       

      A running sum for the revenue:

      rangesum(above(sum({<Year>}REVENUE), 0 ,12))

       

      I constructed this set analysis but I the results are higher than the number of users that entered hours.

      Count({1<USER_NUMBER=P({1<NROFHOURS={">0"}>}USER_NUMBER)>}USER_NUMBER)

       

      I expected this  expression to aggregate over the Dimension Year-Month but that is not the case, given the higher number of users than acutally exists in the data.

      Adding Distinct to the set analysis didn't change anything.

      Count(Distinct {1<USER_NUMBER=P({1<NROFHOURS={">0"}>}USER_NUMBER)>}USER_NUMBER)



      Please advise