Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

Thanks in advance

//A.

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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.

View solution in original post

9 Replies
lironbaram
Partner - Master III
Partner - Master III

hei

try to change it like this

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

hope it helps you

Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

hei try this

=

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

where MaxDate is a Variable with the formula



=max(DateInvoice)-7

this should work

Miguel_Angel_Baeyens

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.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

Hi,

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

YYY-MM-DD