topic SV:Re: Difference between dates in set analysis in QlikView

Difference between dates in set analysis

Tue, 01 Mar 2011 10:26:50 GMT

Hi!

I'm trying to get a sum according to a datefield that decides the activationdate of the invoices to be summarized. I'v got this form a life saver on this forum but it does not work. Any idéas? Or workarounds?

=sum({<InvoiceActivationDate={'>=$(=Date(max(pay_date)-21))<=$(=Date(max(pay_date)-14))'>} OriginalPrice)'

Thanks in advance!

//A.

Difference between dates in set analysis

Tue, 01 Mar 2011 10:57:25 GMT

Can u send a sample of your application.

SV:Re: Difference between dates in set analysis

Tue, 01 Mar 2011 11:06:00 GMT

How do i do that? without exposing the company i work for =).

//A.

Difference between dates in set analysis

deepakk — Tue, 01 Mar 2011 11:09:48 GMT

hi Ankay,

Change the single quotes to double quotes. ie ' >=.... ' to ">= ..." . Also chekc the date format in InvoiceActivationDate field. and put the same format in Date(........... ,'DD/MM/YYYY').

Also i see a single quotes at the end of OrginalPrice)'. Remove it.

Check if it works fine

SV:Re: Difference between dates in set analysis

Tue, 01 Mar 2011 11:19:32 GMT

I chaged it to this;

=sum({<date(InvoiceActivationDate, 'YYYY-MM-DD')={">=$(=Date(max(pay_date)-21))<=$(=Date(max(pay_date)-14))">} OriginalPrice)

was that correct? however, still does not work.

The formats is the same so the YYYY-MM-DD should be redundant.

😞

//A.

SV:Re: Difference between dates in set analysis

deepakk — Tue, 01 Mar 2011 11:22:54 GMT

no.. not on the InvoiceActivationDate field. Check what is the format for the InvoiceActivationDate.

for eg as you said its 'yyyy-mm-dd' then

sum({<InvoiceActivationDate={">=$(=Date(max(pay_date)-21),'YYYY-MM-DD')<=$(=Date(max(pay_date)-14),'YYYY-MM-DD')">} OriginalPrice)

I hope this helps.

SV:Re: SV:Re: Difference between dates in set analysis

Tue, 01 Mar 2011 11:26:18 GMT

Didn't work either... it's the difference that i'm worried about because this set analysis works perfect;

=sum({<InvoiceActivationDate={'>=$(=Date(max(pay_date)-14))'}>} OriginalPrice)

//A.

SV:Re: SV:Re: Difference between dates in set analysis

deepakk — Tue, 01 Mar 2011 11:42:20 GMT

Then check what the maxDate -21 and maxdate-14

Because your above code say >14 and we were trying on <14.

Check the data for the range you require.

SV:Re: SV:Re: SV:Re: Difference between dates in set analysis

Tue, 01 Mar 2011 12:07:20 GMT

That's correct. e.g. say that pay_date is 2011-02-28 then max(pay_date)-21 is 2011-02-07 and max(pay_date)-14 is 2011-02-21

so i want to sum invoices that are activated between those dates InvoiceActivation>2011-02-07 and <2011-02-21

SV:Re: SV:Re: SV:Re: Difference between dates in set analysis

lironbaram — Tue, 01 Mar 2011 13:01:08 GMT

hei

try this one as an example

may be it'll helps you

SV:Re: SV:Re: SV:Re: SV:Re: Difference between dates in set analysis

Tue, 01 Mar 2011 13:44:30 GMT

Thank you so so much!

The reson why i didn't work was the "}" at the end... stupid of me not to see it.

Thanks a lot!!

//A.