Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Make a selection with Range

Hi i have this situation:

i have 2 selectbox:

a. with the years from my DATE_INI_VAL

b. with month from my DATE_INI_VAL

and i have a TABLE where i show all my rows

when i click in my Month, i NOT want select the rows in my table of my Month.... but i want select all my rows in my table that are in this range:

DATE_INI_VAL <= month (selected)   AND   DATE_FIN_VAL >= month (selected)

how can i do that?

1 Solution

Accepted Solutions
Highlighted
Champion
Champion

here is one last approach, it will create a synthetic key but no duplicate lines:

this one will not affect the performance at all.

data:

LOAD FORNITORE,

    [COD. CLIENTE],

    [RAGIONE SOCIALE],

    MIS_MENSILE,

    D_FINE_VAL,

    D_INIZ_VAL

FROM

(biff, embedded labels, table is Sheet1$);

MinMax:

Load

Min(D_INIZ_VAL) as MinDate,

Max(D_FINE_VAL) as MaxDate

Resident data;

Let vMin = FieldValue('MinDate',1);

Let vMax = FieldValue('MaxDate',1);

Let vDays = vMax-vMin+1;

Drop Table MinMax;

Calendar:

Load

Date($(vMin) + RowNo()-1) as Date

AutoGenerate ($(vDays));

left join

load Date,

  year(Date) as Year,

  month(Date) as Month,

    Date(MakeDate(year(Date),month(Date)),'YYYYMM')  as  YearMonth

resident Calendar;

RangeToDate:

intervalmatch (Date) load D_INIZ_VAL, D_FINE_VAL resident data;



just change the last code with all this and let me know.

View solution in original post

29 Replies
Highlighted
Champion
Champion

Hi,

when you compare your dates fields to months, what is the Year used here ? current year ?

Highlighted

Hi Danielle, this can be done with set analysis, ie:

Sum({<DATE_INI_VAL={"<=$(=Max(DATE_INI_VAL))"}, DATE_FIN_VAL={">=$(=Max(DATE_INI_VAL))"}>} Field)

The correct final expression can depend on date formats, data relationships, fields used for selections...

Highlighted
Partner
Partner

Hi,

If I understand you correctly you want to sum values where the DATE_INI_VAL is lower or equal to the selected date and DATE_FIN_VAL is larger or equal to the selected date. The below should do the trick.

Sum({<DATE_INI_VAL = {"<=$(=Max(DATE_INI_VAL))"}, DATE_FIN_VAL = {">=$(=Max(DATE_INI_VAL))"} >}value)

Highlighted
Champion
Champion

Just one thing here: It should be sum(Field) instead of sum(Value)...

Highlighted
Creator III
Creator III

Nono, sorry i not want Sum nothing....

i have a table with rows

i want only show the rows that satisfy my condition, and my conditiion must be:

DATE_INI_VAL (is a column of my table) <= month (selected)   AND   DATE_FIN_VAL (is a column of my table) >= month (selected)

Highlighted
Creator III
Creator III

Yes, or the year i choice from a selection

Highlighted
Champion
Champion

I think the best solution here for you is to create a YearMonth field on the script.

it will simplify this type of analysis.

are you ok or you want to use a Month field and a Year field ?

Highlighted
Creator III
Creator III

For me can be ok to use a YearMonth field on the script, but how can i do it, for make the solution?

can write me the script i need to write?

Thanks

Highlighted
Champion
Champion

if you don't have a master calendar, you can put this directly into your table load:

Num(Date#(DATE_INI_VAL, 'YYYYMM')) as YearMonth

like this you select a range from your Yearmonth listbox, example

from 201701 to 201712

your rows will automatically update to that selection.