Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
souadouert
Specialist
Specialist

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)

18 Replies
big_dreams
Creator III
Creator III

try with if as i said below reply.

souadouert
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Creator III
Creator III

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