Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Hi,
when you compare your dates fields to months, what is the Year used here ? current year ?
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...
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)
Just one thing here: It should be sum(Field) instead of sum(Value)...
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)
Yes, or the year i choice from a selection
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 ?
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
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.