Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
Can you advise me: I need to remove 2 days from final scope every month, so in the end of year it should be -24 days.
Count({<PD_Processed_ind={'Processed'}, STATUS_VERSION = {'Final'},EntryDate={">=$(=Date((Min(DateKey))))<=$(=Date((Max(DateKey))))"}>}distinct([KEY_ZEIC_CCH_URN_PD_WI_ID]))/
(Count({<EntryDate={">=$(=Date((Min(DateKey))))<=$(=Date((Max(DateKey))))"}>}distinct EntryDate)-4)
In Above expression I set "-4" as the result of 2 months, but how is it possible to automise it?
Thanks in advance
How do you get the final scope? Is it Max(DateKey) - Min(DateKey) ? How do values look like in DateKey?
So here is the Calendar Data:
Calendar:
LOAD
AutoNumber("Fiscal Period"&"Fiscal Year") as SortKey,
Num(Date("Period Start",'DD.MM.YYYY')) as [Period Start],
Num(Date("Period End",'DD.MM.YYYY')) as [Period End],
Num("Fiscal Period",'00')&'-'&"Fiscal Year" as [Fiscal Period] ,
AutoNumberHash128([Period Start],[Period End]) as CalendarKey
FROM [Extract_(445_Calendar).qvd]
(qvd);
Let vL.StartDate=Num(MakeDate(2008,1,1));
Let vL.EndDate=Num(Date(Today())+1);
CalendarExpand:
Load
Num(Date($(vL.StartDate)+RecNo()-1)) as DateKey
AutoGenerate(-$(vL.StartDate)+$(vL.EndDate)+1);
NoConcatenate
IntervalMatch_tmp:
IntervalMatch (DateKey)
LOAD [Period Start] , [Period End]
Resident Calendar;
IntervalMatch_Bridge:
Load Distinct
AutoNumberHash128([Period Start],[Period End]) as CalendarKey,
DateKey
Resident IntervalMatch_tmp
Where Exists(DateKey)
;
Inner join (Calendar)
Load Distinct CalendarKey,DateKey Resident IntervalMatch_Bridge;
Drop Tables IntervalMatch_tmp,CalendarExpand,IntervalMatch_Bridge;
Current_Period:
Load distinct
DateKey,
[Period Start] as Period_Start_CP,
[Fiscal Period] as CurrentPeriod,
SortKey as CurrentSortKey
resident Calendar
where DateKey=Today();
let vL.PeriodStart = peek('Period_Start_CP');
Would not just counting the month return the needed results, maybe with something like:
Count({<EntryDate={">=$(=Date((Min(DateKey))))<=$(=Date((Max(DateKey))))"}>}distinct [Fiscal Period]) * 2
- Marcus
Thank you Marcus for your reply, but this is not working, it`s multiply all working days. But EntryDate should be the only one date for counting.
Then create with: month(EntryDate) or something similar a month-field.
- Marcus