22 Replies Latest reply: Dec 15, 2017 5:19 AM by Anand Chouhan

Date compraison

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)

• Re: Date compraison

May be this?

Sum({<CLE = {">=\$(=Date(YearStart(Left(CLE,8)),'YYYYMMDD')) <=\$(=Date(Max(Left(CLE,8)),'YYYYMMDD'))"}>} COMPTE_ENCOURS_TND)

• Re: Date compraison

Hello Anil ,

i tried this expression , but the result 0

• Re: Date compraison

try with if as i said below reply.

• Re: Date compraison

if CLE=20170401A2 then you could not use it in set analysis here

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

Date should be used here instead, your set analysis Expression will not work even if you switch, it need some modifications.

could you give us some sample data from Excel or simular and also expected output.

• Re: Date compraison

But the first thing I should do is to create a New CLE field in script With left(CLE,8) as New_CLE or simular.

It's than easier to use in set analysis, Your DATE and CLE will than have same format.

• Re: Date compraison

Hi,

In your script derived 2 new fields from CLE

Like

Left(CLE,8) as date,

Right(CLE,2) as ID

and then use date field in your script.

Regards

• Re: Date compraison

Hello MAX,

i have not the right to modify the script

• Re: Date compraison

try with if condition

like

Sum(if(left(CLE,8)<\$(=date(Monthstart(Today()))) and left(CLE,8)> 20170101,COMPTE_ENCOURS_TND))

Regards,

• Re: Date compraison

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

• Re: Date compraison

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

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

• Re: Date compraison

Max the rsult is false

• Re: Date compraison

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,

• Re: Date compraison

You need to use RangeSum(Above()) here

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

• Re: Date compraison

ok then I think you required cumulative sum

use rangesum()

Regards,

• Re: Date compraison

or you can select full accumulation from expression window.

Regards

• Re: Date compraison

Please check the date format or else better provide sample data for this.

• Re: Date compraison

As already people suggested, better approach would be creating a date field in the script and then use that in the set analysis. However, if you want to try it strictly in the front-end try putting a TOTAL in your max() and removing the single quotes around number, like:

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

• Re: Date compraison

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

• Re: Date compraison

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

• Re: Date compraison

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

• Re: Date compraison

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