Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
danosoft
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
YoussefBelloum
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
YoussefBelloum
Champion
Champion

Hi,

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

rubenmarin

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

YoussefBelloum
Champion
Champion

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

danosoft
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Yes, or the year i choice from a selection

YoussefBelloum
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 ?

danosoft
Creator III
Creator III
Author

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