topic field equals fields in set analysis in QlikView

field equals fields in set analysis

Mon, 28 Feb 2011 11:21:11 GMT

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.

field equals fields in set analysis

lironbaram — Mon, 28 Feb 2011 11:24:43 GMT

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

Mon, 28 Feb 2011 11:34:12 GMT

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

SV:Re: field equals fields in set analysis

lironbaram — Mon, 28 Feb 2011 11:47:37 GMT

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

Miguel_Angel_Baeyens — Mon, 28 Feb 2011 12:10:45 GMT

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

Mon, 28 Feb 2011 15:28:24 GMT

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

Miguel_Angel_Baeyens — Mon, 28 Feb 2011 15:36:50 GMT

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

Mon, 28 Feb 2011 15:53:34 GMT

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

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

Miguel_Angel_Baeyens — Mon, 28 Feb 2011 16:05:03 GMT

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

Mon, 28 Feb 2011 16:10:16 GMT

Hi,

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

YYY-MM-DD