Get data value not affected by filter (List Box)

[#1]

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)

[swuehl]

Maybe

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

[#3]

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

[swuehl]

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?

[#5]

Yes, date values have dual representation

[swuehl]

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.

[Digvijay_Singh]

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

[Digvijay_Singh]

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