topic Re: Indirect set analysis, nr of users in a month in QlikView

Indirect set analysis, nr of users in a month

Michiel_QV_Fan — Fri, 13 May 2016 11:36:23 GMT

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

Re: Indirect set analysis, nr of users in a month

marcus_sommer — Fri, 13 May 2016 11:54:10 GMT

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

- Marcus

Re: Indirect set analysis, nr of users in a month

Michiel_QV_Fan — Fri, 13 May 2016 11:55:27 GMT

both or just the 1 in the P part?

Re: Indirect set analysis, nr of users in a month

mvanlutterveld — Fri, 13 May 2016 12:10:20 GMT

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

Re: Indirect set analysis, nr of users in a month

marcus_sommer — Fri, 13 May 2016 12:10:59 GMT

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

- Marcus