Solved: Make a selection with Range - Qlik Community

danosoft · ‎2018-03-07

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?

YoussefBelloum · ‎2018-03-09

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

YoussefBelloum · ‎2018-03-07

Hi,

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

rubenmarin · ‎2018-03-07

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

niclaz79 · ‎2018-03-07

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)

YoussefBelloum · ‎2018-03-07

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

danosoft · ‎2018-03-07

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)

danosoft · ‎2018-03-08

Yes, or the year i choice from a selection

YoussefBelloum · ‎2018-03-08

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 ?

danosoft · ‎2018-03-08

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

YoussefBelloum · ‎2018-03-08

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.

Make a selection with Range

Related Topics