topic Re: Make a selection with Range in QlikView

Make a selection with Range

danosoft — Wed, 07 Mar 2018 15:01:49 GMT

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?

Re: Make a selection with Range

YoussefBelloum — Wed, 07 Mar 2018 15:09:10 GMT

Hi,

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

Re: Make a selection with Range

rubenmarin — Wed, 07 Mar 2018 15:12:34 GMT

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

Re: Make a selection with Range

niclaz79 — Wed, 07 Mar 2018 15:25:41 GMT

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)

Re: Make a selection with Range

YoussefBelloum — Wed, 07 Mar 2018 15:32:37 GMT

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

Re: Make a selection with Range

danosoft — Wed, 07 Mar 2018 16:27:47 GMT

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)

Re: Make a selection with Range

danosoft — Thu, 08 Mar 2018 08:10:37 GMT

Yes, or the year i choice from a selection

Re: Make a selection with Range

YoussefBelloum — Thu, 08 Mar 2018 09:00:02 GMT

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 ?

Re: Make a selection with Range

danosoft — Thu, 08 Mar 2018 09:33:46 GMT

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

Re: Make a selection with Range

YoussefBelloum — Thu, 08 Mar 2018 09:43:40 GMT

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.

Re: Make a selection with Range

danosoft — Thu, 08 Mar 2018 09:54:31 GMT

Ok, but this is not the solution..... when i choice the YEAR or the MONTH my table (ora my chart table) need to be filter the rows in this way:

DATE_INI_VAL (the column in table) <= month (selected or YearMonth field) AND DATE_FIN_VAL (the column in table) >= month (selected or YearMonth field)

this need to be the filter

Re: Make a selection with Range

YoussefBelloum — Thu, 08 Mar 2018 09:58:45 GMT

My bad, was just looking at the INI_VAL field...

have you tried the expressions below ?

Re: Make a selection with Range

danosoft — Thu, 08 Mar 2018 10:13:35 GMT

yes i used the expression for others problem... but i not know how to solve this kind of problem for make this kind of filter.....

Re: Make a selection with Range

YoussefBelloum — Thu, 08 Mar 2018 10:16:11 GMT

to go further, it is time to share some data

Re: Make a selection with Range

danosoft — Thu, 08 Mar 2018 10:24:19 GMT

what you mean, what i need to do?

i explain the problem,

i need to have some selection with YEAR and MONTH

when i choice a value of the selection my table (ora my chart table) need to be filter the rows in this way:

DATE_INI_VAL (the column in table) <= month (selected or YearMonth field) AND DATE_FIN_VAL (the column in table) >= month (selected or YearMonth field)

how can i do that?

Re: Make a selection with Range

YoussefBelloum — Thu, 08 Mar 2018 10:46:21 GMT

You need to share some DATA, what is not clear about this ?

94 people opened your thread, 4 peoples tried to help and no one was able to give the output you expect.

So you need to make an effort to share some data that represent your table structure on an excel file for example.

Re: Make a selection with Range

danosoft — Thu, 08 Mar 2018 11:31:15 GMT

Ok, sure sorry

i attach thia XLS with Data

Re: Make a selection with Range

YoussefBelloum — Thu, 08 Mar 2018 14:14:44 GMT

Hi stalwar1‌ can please take a look at this and tell me if it is the best approach ? (this message if just for an expert to validate)

Ok, It was bit more complicated than I expected (that's why I asked for the data structure).

Actually you need an intervalmatch to create a DATE field from your start_date and your end_date

here is the code:

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;

left Join (data)

IntervalMatch (Date)

Load D_INIZ_VAL,

D_FINE_VAL

Resident data;

now your table is connected to a master calendar like this:

and here is the expected output:

PLEASE FIND ATTACHED the QVW

Re: Make a selection with Range

danosoft — Fri, 09 Mar 2018 08:02:33 GMT

Thanks, i think can be ok, i am tring it, but i have a problem... when i execute that with all real data, this is the details:

CONTR_Gas << V_GAS_DATA 11.970 linee recuperate

MinMax << CONTR_Gas 1 linee recuperate

Calendar << AUTOGENERATE(2556) 2.556 linee recuperate

Calendar 2.556 linee recuperate

CONTR_Gas 3.863.325 linee recuperate

when it finish to execute and open in layout my table.... it not show me nothing becouse it say that the object is out of memory.....

Re: Make a selection with Range

YoussefBelloum — Fri, 09 Mar 2018 08:50:00 GMT

Yes the disadvantage of this technique is the duplication of lines..

you can set a calculation condition on your table, example: not display data before you make selection on the YearMonthfield, like that the lines on the table are reduced and displayed.

put this into the calculation condition of your table:

=GetSelectedCount(YearMonth)<>0