Date compraison

[souadouert]

I have a problem with a compound CLE field:

dateID example

20170401 (DATE)

A2 (ID)

CLE: 20170401A2

so I need to extract the date 20170401 and sum the amount from 01/01/2017 to 20170401 (YTD)

Sum({<CLE= {"=Left(CLE,8) > '20170101' and  <=$(=max(Left(CLE,8) ))"} >} COMPTE_ENCOURS_TND)

[big_dreams]

try with if as i said below reply.

[souadouert]

Hello Trsesco,
i tried yoursolution but nothing changed , and i have not the right to change the script

[sunny_talwar]

Why not just perform set analysis on your Date field? Why perform it on CLE?

[souadouert]

MAX THANK YOU I USED THIS EXPRESSION

Sum(if(left(CLE,8)<=$(=max( Left(CLE,8) )) and left(CLE,8)>= 20170101 and CREDIT_NATURE='IMMO',CREDIT_MONTANT_PRODUCTION,0))

[souadouert]

HELLO SUNNY
THE PROBLEM ,I HAVE NOT DATE FIELD AND I HAVE NOT THE RIGHET TO MODIFY THE SCRIPT ? BUT I AM GOING TO PROPOSE ADDING THIS FIELD

[souadouert]

Max the rsult is false

[sunny_talwar]

If this worked, then I think that this should work also...

Sum({<CLE = {"=Left(CLE,8) <= $(=Max(Left(CLE, 8))) and Left(CLE, 😎 >= 20170101 and CREDIT_NATURE = 'IMMO' "}>} CREDIT_MONTANT_PRODUCTION)

[big_dreams]

ok.

1st we Try with static value then we make it dynamic

Sum(if(left(CLE,8)<'20171130' and left(CLE,8)>= '20170101' and CREDIT_NATURE='IMMO',CREDIT_MONTANT_PRODUCTION,0))

Note : Please careful with brackets and date format.

Regards,

[its_anandrjs]

Try this way for year start use the YearStart(Left(Today(),10))

=Sum({<CLE= {">=$(=Date(YearStart(Left(Today(),10)))) <=$(=Date(Max(Left(CLE,8))))"}>} COMPTE_ENCOURS_TND))