Hi every one,
I have 2 date Columns and one calendar table I had treid to create filter Month and Year. In my Calendar I have the year, Month column in my Table I have 2 date Column. when I connect my calender Year Column to one of this date column it only filter of this column but not both of them. I want to know how I should connect but Columns date to Calender column that it filter both of them.
in the following I will write my Table code and my Calender code:
this is my Table named Ticket :
TICKET:
Load *
,Date#(Date(Floor(([%Datum])),'YYYY.MMM')) as JahrMonat
,Date(Monthstart([%Datum]),'YYYY.MMM') as Jahr_Monat // sorting check Numeric ascending/descending
//, year(datum) & '.'& num(Month(datum)) as Jahr_Monat // Sorting State and alphabetic
,Year([%Datum]) as jahr
,Month([%Datum]) as Monat
,num([%Datum]) as NumDate
;
LOAD "PROJECT_NAME",
"STATUS_NAME",
"GUELTIG_VON",
"GUELTIG_BIS" as [%Datum],
"GUELTIG_BIS" ,
"VERSION_NAME",
"TICKET_ID",
"RELEASE_NAME",
"PRIOTITY_NAME",
"USER_ID",
TEAM,
BEREICH,
"DATUM_VON",
"DATUM_BIS",
"AUTHOR_ID",
GRUPPE,
LOADDATEFIX;
SQL SELECT "PROJECT_NAME",
"STATUS_NAME",
"GUELTIG_VON",
"GUELTIG_BIS",
"VERSION_NAME",
"TICKET_ID",
"RELEASE_NAME",
"PRIOTITY_NAME",
"USER_ID",
TEAM,
BEREICH,
"DATUM_VON",
"DATUM_BIS",
"AUTHOR_ID",
GRUPPE,
LOADDATEFIX
FROM "DWH_REDMINE_TEST".TICKET
where USER_ID IN (94);
and this is my Calender Table:
LET vMinDate = '01.01.2014';
// Der Kalender läuft bis zum letzten Tag des laufenden Monats
LET vMaxDate = ceil(MonthEnd(Today()));
// Generate all Dates from vMindate to vMaxdate
DatumTemp:
LOAD date($(#vMinDate)+IterNo()-1) AS DatumTemp
AUTOGENERATE (1)
WHILE $(#vMinDate)+IterNo()-1 <= $(#vMaxDate);
AMV_KALENDER:
Load
*
,AutoNumber(EindeutigesQuartal, 'QuartalID') as [QuartalID]
,AutoNumber(EindeutigerMonat, 'AutoMonatID') as [AutoMonatID]
;
LOAD
Week(DatumTemp) AS Kalenderwoche,
Year(DatumTemp) AS Kalenderjahr,
Month(DatumTemp) AS Kalendermonat,
ceil(month(DatumTemp)/3) as Kalenderquartal,
Weekday(DatumTemp) AS Wochentag,
year(DatumTemp) &'-' & ceil(month(DatumTemp)/3) as EindeutigesQuartal,
year(DatumTemp) &'-' & (ceil(month(DatumTemp)/3)+1) as EindeutigesFolgeQartal,
year(DatumTemp) &'-' & ceil(month(DatumTemp)) as EindeutigerMonat,
year(yearstart(date#(DatumTemp),+1,10)) as MLPjahr,
//DatumTemp AS Datum,
DatumTemp AS %Datum
RESIDENT DatumTemp;
DROP TABLE DatumTemp;
I had connect two %Datum Column in order to create connection between this two Tables it Works but only filter this column:
"GUELTIG_BIS" as [%Datum]
I have an other date Column that name :
"GUELTIG_VON"
but my Filter Monat just change of my "GUELTIG_BIS" column.
IS there any person who could guide me Please
