topic Re: Get data value not affected by filter (List Box) in QlikView

Get data value not affected by filter (List Box)

Mon, 01 Jun 2015 15:38:46 GMT

Hello,

Please provide your opinion on the following issue below.

I have a table with the following columns. This table is used in my file's Load Script:

Date,

Data,

CurrentMonth

I also have a List Box that is using CurrentMonth. This List Box is used as a filter.

This is the task I'm trying to accomplish:

When I select a specific CurrentMonth value I need to get the following:

find a Min (Date), subtract from it 7 days and get a Sum (Data) for the date I I get (Min (Date) - 7)

Re: Get data value not affected by filter (List Box)

swuehl — Mon, 01 Jun 2015 15:41:19 GMT

Maybe

=sum({1<Date = {'$(=Date(min(Date)-7))'}>} Data)

Re: Get data value not affected by filter (List Box)

Mon, 01 Jun 2015 15:58:39 GMT

Sorry, it doesn't work. I get zero value

Re: Get data value not affected by filter (List Box)

swuehl — Mon, 01 Jun 2015 16:11:29 GMT

Have your Date field values a dual representation, with a text value like 06/01/2015 and a numeric representation?

Why don’t my dates work?

Re: Get data value not affected by filter (List Box)

Mon, 01 Jun 2015 16:32:35 GMT

Yes, date values have dual representation

Re: Get data value not affected by filter (List Box)

swuehl — Mon, 01 Jun 2015 16:40:01 GMT

Ok, then the format of the values in Date field must match the format which is used in the set expression.

Check

=Date(min(Date)-7)

it should return a date with a format used in your Date field, e.g. MM/DD/YYYY.

If the format used in your field differs from the default format, you need to specify this format as argument to Date() function:

=Date(min(Date)-7,'MM/DD/YYYY')

Finally, this date returned needs to have a counter part in your Date field, you need to have records with Data values for that date.

Re: Get data value not affected by filter (List Box)

Digvijay_Singh — Mon, 01 Jun 2015 17:04:57 GMT

Hi,

I tried the expression as mentioned in the reply to your question i.e. =sum({1<Date = {'$(=Date(min(Date)-7))'}>}Data) , Seems to be working fine.

Please check the attached app. I used following inline data for example, it showed 7500 when I select feb in list box -

LOAD * INLINE [
Date, Data, CurrentMonth
01-01-2015, 100, Jan
25-01-2015, 1500, Jan
25-01-2015, 1500, Jan
25-01-2015, 1500, Jan
25-01-2015, 1500, Jan
25-01-2015, 1500, Jan
01-02-2015, 200, Feb
05-02-2015, 300, Feb
10-02-2015, 400, Feb
01-03-2015, 300, Mar

Re: Get data value not affected by filter (List Box)

Digvijay_Singh — Mon, 01 Jun 2015 17:08:52 GMT

Thanks Swuehl, I am new to QlikView, got some learning from your response.