Set analysis with field value equal to field value

[justalkak]

Hi All,

Can you suggest how I should build the set analysis function in order to have the same result as I can achieve by function:

count(if([Hire Date Year Month Num] = YearMonthNum, [Employee ID]))

so that I can calculate how many employees have the Hire Month equal to particular calendar month?

I have a trouble with condition field = field.

Thank you

[BrunPierre]

Count({$<[Hire Date Year Month Num]=P(YearMonthNum)>} [Employee ID])

or

Count({$<[Hire Date Year Month Num]={"=[Hire Date Year Month Num]=YearMonthNum"}>} [Employee ID])

[justalkak]

Almost, but not in 100%.

The first solution Count({$<[Hire Date Year Month Num]=P(YearMonthNum)>} [Employee ID]) calculates well when no dimesions given or when dimension is Hire Date Year Month. However, I need to have a table with YearMonth as a dimension.. How to change that (changing the places of HDYMN with YMN in the formula doesnt help).

The second solution works little bit worse - it calculates only for the latest YM / HDYM.

Can you please suggest modification?

[BrunPierre]

How about this?

=Count({$<YearMonth = {"=[Hire Date Year Month Num]=P(YearMonthNum)"}>}[Employee ID])

[justalkak]

Nope.. gives only zeros.. ;/

[BrunPierre]

Count(Aggr(Count({$<[Hire Date Year Month Num]=P(YearMonthNum)>} [Employee ID]),YearMonth))

[marcus_sommer]

It couldn't be solved with a "classical" set analysis because set analysis worked like a selection on a column-level and not on a row-level. Of course, there are ways to transfer this logic into a set analysis syntax but it will remain an if-loop.

Therefore, I suggest remaining by an if-loop whereby it could be optimized by moving the condition to the outside, like:

if([Hire Date Year Month Num] = YearMonthNum, count([Employee ID]))

- Marcus