Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
MVP
MVP

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.

9 Replies
lironbaram
Honored Contributor II

field equals fields in set analysis

hei

try to change it like this

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

hope it helps you

Not applicable

SV:Re: field equals fields in set analysis

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

lironbaram
Honored Contributor II

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

MVP
MVP

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.

Not applicable

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!

MVP
MVP

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.

Not applicable

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

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

MVP
MVP

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.

Not applicable

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

Community Browser