Difference between dates in set analysis

[#1]

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.

[#2]

Hi

Can u send a sample of your application.

[#3]

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

//A.

[deepakk]

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

[#5]

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.

[deepakk]

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.

[#7]

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]

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.

[#9]

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 · Accepted solution]

hei

try this one as an example

may be it'll helps you