9 Replies Latest reply: Feb 28, 2011 11:10 AM by AnnikaBorg

# field equals fields in set analysis

Hi,
I can't seem to get my set analysis right here's what i do;

=sum({<InvoiceActivationDate={\$<=(max(pay_date)-7)}>} OriginalPrice)

So what I want to do is sum OriginalPrice for dates (InvoiceActivationsDate's) that are earlier than the maximum pay_date -7 days.

I've tried to just write;

=sum({<InvoiceActivationDate={\$(max(date))}>} OriginalPrice)

but that doesn't work either.

Where do i go wrong?

//A.

• ###### field equals fields in set analysis

hei

try to change it like this

=sum({<InvoiceActivationDate={"<=\$(=(max(pay_date)-7)"}>} OriginalPrice)

hope it helps you

• ###### SV:Re: field equals fields in set analysis

didn't work :(
i only get "-" in the expression column...

• ###### SV:Re: field equals fields in set analysis

hei try this

=

sum({<DateInvoice={"<=\$(#MaxDate)"}>} OriginalPrice)

where MaxDate is a Variable with the formula

=max(DateInvoice)-7

this should work

• ###### SV:Re: field equals fields in set analysis

Hello,

First, make sure that both InvoiceActivationDate and Max(Date) return the result in the same format. My guess is that it's not, because Max(Date) will return an integer. So something like the following would work, provided InvoiceActivationDate as a DD/MM/YYYY date format.

`sum({<InvoiceActivationDate={'\$(=Date(max(date)))'}>} OriginalPrice)`

Should this doesn't work, you will need first to format InvoiceActivationDate to the same format "date" is, likely in the script (it will save further time of calculation when rendering the chart).

Note: When you call a function within the left part of a set analysis, you need to use \$(=Function()) to get it work.

Hope that helps.

• ###### SV:Re: SV:Re: field equals fields in set analysis

Thank you guys! You are life savers. But i have a following problem;

Now i want to sum original price for activation dates between to dates;

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

i guess i'm doing the "and" thing wrong.

Thanks again!

• ###### SV:Re: SV:Re: field equals fields in set analysis

Hello,

Check the >= and <= syntax below

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

Hope that helps.

• ###### SV:Re: SV:Re: SV:Re: field equals fields in set analysis

I copied the exact script but i didn't work. :(

• ###### SV:Re: SV:Re: SV:Re: field equals fields in set analysis

Hello,

Make sure that the Date(Max(pay_date) -14) expression returns something with the same format that your InvoiceActivationDate values. Otherwise you will need to format it in the set analysis.

Hope that helps.

• ###### SV:Re: SV:Re: SV:Re: SV:Re: field equals fields in set analysis

Hi,

It does, because i have it as a statement in the header of the column.

YYY-MM-DD