topic Re: Set analysis with field value equal to field value in Connectivity & Data Prep

Set analysis with field value equal to field value

justalkak — Mon, 14 Nov 2022 13:17:04 GMT

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

Re: Set analysis with field value equal to field value

BrunPierre — Mon, 14 Nov 2022 13:42:00 GMT

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

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

Re: Set analysis with field value equal to field value

justalkak — Fri, 18 Nov 2022 21:48:08 GMT

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?

Re: Set analysis with field value equal to field value

BrunPierre — Sat, 19 Nov 2022 01:27:12 GMT

How about this?

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

Re: Set analysis with field value equal to field value

justalkak — Sun, 20 Nov 2022 14:38:43 GMT

Nope.. gives only zeros.. ;/

Re: Set analysis with field value equal to field value

BrunPierre — Sun, 20 Nov 2022 15:11:06 GMT

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

Re: Set analysis with field value equal to field value

marcus_sommer — Mon, 21 Nov 2022 12:30:46 GMT

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