find a value in aggr result

[#1]

Dear All,

I have a requirement to find all the customers who have closed all their accounts in a specific reporting period. for this I am using below expression :

if (match('Active',Concat($(vAcc_Close),','))=0, CUS_IDR),CUS_IDR)

Variable vAcc_Close returns status of the accounts and I am expecting if they have an account with Active stauts the above expression returns >0.

Although Concat($(vAcc_Close,',' )) returns Active,Active for a customer but abovematch expression returns 0.

I know I am missing something here but not sure what.

any help on this would be appreciated.

Thanks in advance

Neda

[sunny_talwar]

Is this a dimension here? May be you need to use TOTAL if this is a calculated dimension?

If(Match('Active', Concat(TOTAL $(vAcc_Close),','))=0, CUS_IDR),CUS_IDR)

[#3]

Hi Sunny,

Thanks for the reply. dimension is date, I have added total but no difference in the result. I think I need to add something before Concat to return the answer set but not sure how. The fact that Concat is returning the expected result but when using Match function against that is not working, makes me think I'll need to somehow evaluate the Concat as a variable?

[sunny_talwar]

My bad, how about this:

If(SubStringCount(Concat(DISTINCT $(vAcc_Close),','), 'Active') = 0, CUS_IDR)

[#5]

Sorry, still not working

[sunny_talwar]

Do you have a sample you can share?

[#7]

No sorry, company regulation!

looking at similar posts suggets using sum before match but still not working!

[sunny_talwar]

Okay

[vishsaggi]

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Preparing examples for Upload - Reduction and Data Scrambling

[swuehl]

Maybe

= Concat( Aggr(

      If(SubStringCount(Concat(DISTINCT $(vAcc_Close),','), 'Active') = 0, CUS_IDR)

,CUS_IDR, DATE))

edit:

It would also be good to see your definition of vAcc_Close and the context of the required expression.