Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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
Champion III
Champion III

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