topic Between dates in QlikView

Between dates

schumi1980 — Wed, 25 Nov 2020 16:16:04 GMT

Dear All

I am trying to calculate the premium for the policies expiring between 01.01.2018 and 31.01.2018.

I have entered into my formula:

sum({$<ExpiryDate={'<=$(=01.01.2018)>=$(=30.03.2018)'},[Quote Status]={'Renewal'}>}NetWrtPrem*NWP_FX)

but it gives me wrong outcomes.

Can somebody tell me how to correct his formula?

Many thanks.

Best regards,

Jan

Re: Between dates

YoussefBelloum — Mon, 12 Mar 2018 16:37:26 GMT

Hi,

try this:

sum({$<ExpiryDate={"$(= '>=' & '01/01/2018' & '<=' & '31.01.2018')"},[Quote Status]={'Renewal'}>}NetWrtPrem*NWP_FX)

Make sure that your ExpiryDate field have this format: DD.MM.YYYY

Re: Between dates

sunny_talwar — Mon, 12 Mar 2018 16:38:42 GMT

May be this

Sum({$<ExpiryDate = {">=01.01.2018<=30.03.2018"},[Quote Status] = {'Renewal'}>} NetWrtPrem*NWP_FX)

Re: Between dates

YoussefBelloum — Mon, 12 Mar 2018 17:01:05 GMT

Hi stalwar1‌,

can you please tell us the main difference between these 2 working solutions ?

I use both depending on the result I have..

Re: Between dates

sunny_talwar — Mon, 12 Mar 2018 17:02:34 GMT

They are the same exact solution... you are using a dollar sign expansion... which will be evaluated to what I have ... so they are two ways of writing the same thing

Re: Between dates

YoussefBelloum — Mon, 12 Mar 2018 17:03:45 GMT

thank you

Re: Between dates

sasiparupudi1 — Mon, 12 Mar 2018 21:14:32 GMT

sum({$<ExpiryDate={'>=''01.01.2018''<=''30.03.2018'''},[Quote Status]={'Renewal'}>}NetWrtPrem*NWP_FX)