Issue with Multiple Count condition

[Anonymous]

Hi

I have written the below condition in calculated field.Its nor showing an output or error.

I am able to get the output if i enter single condition(i.e

Count(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL' and [(CASEWHEN(SUM(AL2.TXN_CNT))>0THEN'Y'ELSE'N'END)]='N',ACCT))/

Count(total(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL',ACCT))))

as soon as i add the rest condition,its showing null.

Code:

Count(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL' and [(CASEWHEN(SUM(AL2.TXN_CNT))>0THEN'Y'ELSE'N'END)]='N',ACCT))/

Count(total(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL',ACCT,

Count(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL' and [(CASEWHEN(SUM(AL2.TXN_CNT))>0THEN'Y'ELSE'N'END)]='Y',ACCT))/

Count(total(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL',ACCT,

Count(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' and [(CASEWHEN(SUM(AL2.TXN_CNT))>0THEN'Y'ELSE'N'END)]='N',ACCT))/

count(total(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' ,ACCT,

Count(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' and [(CASEWHEN(SUM(AL2.TXN_CNT))>0THEN'Y'ELSE'N'END)]='Y',ACCT))/

count(total(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' ,ACCT

))))))))))))

[etrotter]

have you tried putting 'or' between the conditions?

[Anonymous]

Hi Erica

I have tried putting 'or' as well 'and' but its not working

[MK9885]

That's a really big expression to even break it down....

Why not use Set analysis with If condition?

If(Count({<Dimension1={'...Value....'}, Dimension2 = {'...Value2...'}>}

    YourDimensiontobeCalculatedFrom) = Conditionhere,'Else Conditionhere'

Maybe ask for someone who knows very well about set analysis.

stalwar1‌

I guess he can help you break it down into simple expression or someone else?

[Anonymous]

i will try

[sunny_talwar]

So you are saying that this is working?

Count(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL' and [(CASEWHEN(SUM(AL2.TXN_CNT))>0THEN'Y'ELSE'N'END)]='N',ACCT))/

Count(total(IF([(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]='Non PAS' and REG_CAT_COARSE='PERSONAL',ACCT))))

[Anonymous]

Yes

[sunny_talwar]

Just out of curiosity, is this a name of a field in your dashboard?

[(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]

[Anonymous]

Yeah that is the calculated field name....I have not renamed it

[sunny_talwar]

That is what is complicating it for all of us.... what exactly are you trying to do here? I have simplified your expression down to this

Count(IF(FieldName1='Non PAS' and REG_CAT_COARSE='PERSONAL' and FieldName2='N',ACCT))/

Count(total(IF(FieldName1='Non PAS' and REG_CAT_COARSE='PERSONAL',ACCT,

Count(IF(FieldName1='Non PAS' and REG_CAT_COARSE='PERSONAL' and FieldName2='Y',ACCT))/

Count(total(IF(FieldName1='Non PAS' and REG_CAT_COARSE='PERSONAL',ACCT,

Count(IF(FieldName1='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' and FieldName2='N',ACCT))/

count(total(IF(FieldName1='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' ,ACCT,

Count(IF(FieldName1='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' and FieldName2='Y',ACCT))/

count(total(IF(FieldName1='Non PAS' and REG_CAT_COARSE='IRA' and POE_CAT='RETIRE INT ROLLOVER','IRA - Internal Rollover' ,ACCT

))))))))))))

Where

FieldName1 =

[(CASEWHENAL1.BUS_LINE='PAS'THEN'PAS'WHENAL1.BUS_LINE='SMAF'THEN'PAS'WHENAL1.BUS_LINE='UMA'THEN'PAS'WHENAL1.BUS_LINE='UMAT'THEN'PAS'WHENAL1.BUS_LINE='BDI'THEN'PAS'WHENAL1.BUS_LINE='SMEI'THEN'PAS'WHENAL1.BUS_LINE='SMLC'THEN'PAS'WHENAL1.BUS_LINE='DMA'THEN'PA]

FieldName2 =

[(CASEWHEN(SUM(AL2.TXN_CNT))>0THEN'Y'ELSE'N'END)]