Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Could be OR condtion is the problem, Can you attach sample to test
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')