Calculated Dimension Grouping

[Anonymous]

Hello Folks,

I am trying to create a calculated dimension with the below logic and name of calculated dimension is Account Group. However, the calculation is not turning out to be correct:

Calculated Dimension:

IF((ACCOUNT_NUM >= 940100 and ACCOUNT_NUM <= 940190 or ACCOUNT_NUM >= 940200 and ACCOUNT_NUM <= 940700), 'Productive Hours',

IF((ACCOUNT_NUM >= 940100 and ACCOUNT_NUM <= 940190 or ACCOUNT_NUM >= 940200 and ACCOUNT_NUM <= 940700 or ACCOUNT_NUM >= 941000 and ACCOUNT_NUM <= 941300 or ACCOUNT_NUM = 943000 ) , 'Paid Hours',

IF((ACCOUNT_NUM >= '960100' and ACCOUNT_NUM <= '960700' OR

  ACCOUNT_NUM >= 962100 and ACCOUNT_NUM <= 962700 OR

  ACCOUNT_NUM >= 964100 and ACCOUNT_NUM <= 964700 OR

  ACCOUNT_NUM >= 966100 and ACCOUNT_NUM <= 966700 OR

  ACCOUNT_NUM >= 970310 and ACCOUNT_NUM <= 970370 OR

  ACCOUNT_NUM >= 970010 and ACCOUNT_NUM <= 970070 OR

  ACCOUNT_NUM >= 970210 and ACCOUNT_NUM <= 970270 OR

  ACCOUNT_NUM >= 970110 and ACCOUNT_NUM <= 970170 OR

  ACCOUNT_NUM >= 970510 and ACCOUNT_NUM <= 970570 OR

  ACCOUNT_NUM >= 970610 and ACCOUNT_NUM <= 970670 OR

  ACCOUNT_NUM >= 970710 and ACCOUNT_NUM <= 970770 OR

  ACCOUNT_NUM >= 970810 and ACCOUNT_NUM <= 970870 OR

  ACCOUNT_NUM >= 970410 and ACCOUNT_NUM <= 970470 ) , 'Productive FTE')

Expression is : SUM(AMOUNT)

Output I am expecting is:

Account Group                Amount:

Paid Hours                      14,436

Productive FTE               2257

Productive Hours            13435

The Output I get is:

Output I am getting is:

Account Group                Amount:

Paid Hours                      1001

Productive FTE               2257

Productive Hours            13435

On Analysis, I found that 1001 + 13435 would make 14,436, and If we look closesly at the account grouping Productive Hours is subset of Paid Hours.

Please advise If I am doing any thing wrong in my calculation?

Thanks,

-Zaffer

[Anil_Babu_Samineni]

Could be OR condtion is the problem, Can you attach sample to test

[sunny_talwar]

May be try this

If((ACCOUNT_NUM >= 940100 and ACCOUNT_NUM <= 940190) or (ACCOUNT_NUM >= 940200 and ACCOUNT_NUM <= 940700), 'Productive Hours',

IF((ACCOUNT_NUM >= 940100 and ACCOUNT_NUM <= 940190) or (ACCOUNT_NUM >= 940200 and ACCOUNT_NUM <= 940700) or (ACCOUNT_NUM >= 941000 and ACCOUNT_NUM <= 941300) or (ACCOUNT_NUM = 943000) , 'Paid Hours',

IF((ACCOUNT_NUM >= '960100' and ACCOUNT_NUM <= '960700') OR

  (ACCOUNT_NUM >= 962100 and ACCOUNT_NUM <= 962700) OR

  (ACCOUNT_NUM >= 964100 and ACCOUNT_NUM <= 964700) OR

  (ACCOUNT_NUM >= 966100 and ACCOUNT_NUM <= 966700) OR

  (ACCOUNT_NUM >= 970310 and ACCOUNT_NUM <= 970370) OR

  (ACCOUNT_NUM >= 970010 and ACCOUNT_NUM <= 970070) OR

  (ACCOUNT_NUM >= 970210 and ACCOUNT_NUM <= 970270) OR

  (ACCOUNT_NUM >= 970110 and ACCOUNT_NUM <= 970170) OR

  (ACCOUNT_NUM >= 970510 and ACCOUNT_NUM <= 970570) OR

  (ACCOUNT_NUM >= 970610 and ACCOUNT_NUM <= 970670) OR

  (ACCOUNT_NUM >= 970710 and ACCOUNT_NUM <= 970770) OR

  (ACCOUNT_NUM >= 970810 and ACCOUNT_NUM <= 970870) OR

  (ACCOUNT_NUM >= 970410 and ACCOUNT_NUM <= 970470) , 'Productive FTE')