Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Difference between dates in set analysis

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.



Tags (2)
1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

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

hei

try this one as an example

may be it'll helps you

10 Replies
Not applicable

Difference between dates in set analysis

Hi

Can u send a sample of your application.

Not applicable

SV:Re: Difference between dates in set analysis

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

//A.

deepakk
Valued Contributor III

Difference between dates in set analysis

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

Not applicable

SV:Re: Difference between dates in set analysis

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.

Smiley Sad

//A.



deepakk
Valued Contributor III

SV:Re: Difference between dates in set analysis

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.

Not applicable

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

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.



deepakk
Valued Contributor III

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

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.

Not applicable

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

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

lironbaram
Honored Contributor II

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

hei

try this one as an example

may be it'll helps you

Community Browser