Comparing a date field with max of an unrelated date field

[#1]

Helloooooooooo,

Basically i have two unrelated date fields and i would like to compare the two. So if ACCOUNT_OPEN_DATE <= max(TRADE_DATE) then i want count the number of clients.

//I would like to do something like this

count (distinct if( ACCOUNT_OPEN_DATE <= max(TRADE_DATE) , CLIENT))

When i do the above it doesnt show any data. Any Ideas??

Regards

Faisal

PS: CLIENT and ACCOUNT_OPEN_DATE fields are both in the same table but TRADE_DATE is in another unrelated table

[rajni_batra]

You cant't compare any two unrelated fields they must be linked withh each other through any primary key or any other linked key

[#3]

Okay so now i have linked the two tables using a TRADER_KEY. But it still doesnt work.

[rajni_batra]

Can u send the sample app

[#5]

Rajni,

Please find below the sample file.  The IF statement is in the guage chart. I hope you can come up with a solution

Regards

Faisal

[rajni_batra · Accepted solution]

PFA

Hope this could help u!!!

[#7]

I think it works.. ill just check. Could you please tell me what this code in your guage chart means ?

{<DATE_OF_OPENING = {"<=$(=max(TRADE_DATE))"}>}

[rajni_batra]

equivalent to if condition when

date of opening <= max(trade date) then count

this is just in format of set nothing else

[rajni_batra]

you can also use

=Count(distinct if(DATE_OF_OPENING <=vmaxdate,CLIENT_CODE))

where vmaxdate= max(trade date)

[#10]

Thanks yo!!