Make a selection with Range

[danosoft]

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?

[danosoft]

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

[YoussefBelloum]

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

have you tried the expressions below ?

[danosoft]

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

[YoussefBelloum]

to go further, it is time to share some data

[danosoft]

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?

[YoussefBelloum]

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.

[danosoft]

Ok, sure sorry

i attach thia XLS with Data

[YoussefBelloum]

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

[danosoft]

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

[YoussefBelloum]

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