field equals fields in set analysis

[#1]

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?

Thanks in advance

//A.

[lironbaram]

hei

try to change it like this

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

hope it helps you

[#3]

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

[lironbaram]

hei try this

=

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

where MaxDate is a Variable with the formula

=max(DateInvoice)-7

this should work

[Miguel_Angel_Baeyens · Accepted solution]

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.

[#6]

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!

[Miguel_Angel_Baeyens]

Hello,

Check the >= and <= syntax below

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

Hope that helps.

[#8]

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

[Miguel_Angel_Baeyens]

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.

[#10]

Hi,

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

YYY-MM-DD