topic Calculated Dimension Grouping in QlikView

Calculated Dimension Grouping

Anonymous — Mon, 25 Dec 2017 21:55:59 GMT

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

Re: Calculated Dimension Grouping

Anil_Babu_Samineni — Mon, 08 Jan 2018 13:39:32 GMT

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

Re: Calculated Dimension Grouping

sunny_talwar — Mon, 08 Jan 2018 13:43:23 GMT

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')