How to add "-2" value every month

[ivanaksen]

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

[tresesco]

How do you get the final scope? Is it Max(DateKey) - Min(DateKey) ? How do values look like in DateKey?

[ivanaksen]

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');

[marcus_sommer]

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

[ivanaksen]

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.

[marcus_sommer]

Then create with: month(EntryDate) or something similar a month-field.

- Marcus