Indirect set analysis, nr of users in a month

[Michiel_QV_Fan]

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

[marcus_sommer]

You used a 1 as set identifier which meant that selections/dimensions will be ignored - try it without the 1.

- Marcus

[Michiel_QV_Fan]

both or just the 1 in the P part?

[mvanlutterveld]

Maybe a somewhat different approach will help you.

Based on a couple of assumptions I think you can solve this requirement within the script. The main assumption is that the Hour declaration is administrated per user by date. Use the declaration date to link to the master calendar. For every date you now have the hours declared per user. The actual number of users each month is a simple count(distinct USER_NUMBER).

The source of Revenue isn’t clear to me, but I assume that is has something to do with the number of declared hours, so Sum(NROFHOURS) should do the trick. Divide the Sum(NROFHOURS) by the count(distinct USER_NUMBER) (if needed do a rangesum / rangecount) and you’re there.

I noticed that you checkif the NROFHOURS > 0. If these records aren’t needed, skip these when loading the declared hours records. If these records can’t be skipped, create an indicator like If(NROFHOURS = 0,1,0) as IndNoHours and use this indicator in a set analyses to rule them out from the calculation (something like count({<IndNoHours = {0}>} distinct USER_NUMBER).

Cheers,

Michiel

[marcus_sommer]

It depends on your objects/selections but I would at first remove both and then check the expression-results.

- Marcus