Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
Can u send a sample of your application.
How do i do that? without exposing the company i work for =).
//A.
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
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.
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.
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.
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.
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
hei
try this one as an example
may be it'll helps you