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)

[Anil_Babu_Samineni]

May be this?

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

[stabben23]

Hi Souad,

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.

[stabben23]

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.

[big_dreams]

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

[its_anandrjs]

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

[tresesco]

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)

[souadouert]

Hello MAX,

i have not the right to modify the script

[big_dreams]

try with if condition

like

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

Regards,

[souadouert]

Hello Anil ,

i tried this expression , but the result 0