topic Re: Date compraison in QlikView

Date compraison

souadouert — Fri, 15 Dec 2017 08:36:59 GMT

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

Anil_Babu_Samineni — Fri, 15 Dec 2017 08:47:16 GMT

May be this?

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

Re: Date compraison

stabben23 — Fri, 15 Dec 2017 09:08:36 GMT

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.

Re: Date compraison

stabben23 — Fri, 15 Dec 2017 09:14:32 GMT

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

big_dreams — Fri, 15 Dec 2017 09:14:43 GMT

Hi,

In your script derived 2 new fields from CLE

Left(CLE,8) as date,

Right(CLE,2) as ID

and then use date field in your script.

Regards

Re: Date compraison

its_anandrjs — Fri, 15 Dec 2017 09:14:49 GMT

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

Re: Date compraison

tresB — Fri, 15 Dec 2017 09:23:54 GMT

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

souadouert — Fri, 15 Dec 2017 09:34:07 GMT

Hello MAX,

i have not the right to modify the script

Re: Date compraison

big_dreams — Fri, 15 Dec 2017 09:49:25 GMT

try with if condition

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

Regards,

Re: Date compraison

souadouert — Fri, 15 Dec 2017 09:50:05 GMT

Hello Anil ,

i tried this expression , but the result 0

Re: Date compraison

big_dreams — Fri, 15 Dec 2017 09:51:39 GMT

try with if as i said below reply.

Re: Date compraison

souadouert — Fri, 15 Dec 2017 09:51:51 GMT

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

Re: Date compraison

sunny_talwar — Fri, 15 Dec 2017 09:53:06 GMT

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

Re: Date compraison

souadouert — Fri, 15 Dec 2017 10:01:35 GMT

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

souadouert — Fri, 15 Dec 2017 10:03:10 GMT

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

souadouert — Fri, 15 Dec 2017 10:09:05 GMT

Max the rsult is false

Re: Date compraison

sunny_talwar — Fri, 15 Dec 2017 10:09:37 GMT

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)

Re: Date compraison

big_dreams — Fri, 15 Dec 2017 10:15:50 GMT

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

its_anandrjs — Fri, 15 Dec 2017 10:19:31 GMT

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