Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to add "-2" value every month

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

Tags (1)
Labels (1)
5 Replies
Highlighted
MVP
MVP

Re: How to add "-2" value every month

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

Highlighted
Contributor III
Contributor III

Re: How to add "-2" value every month

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: How to add "-2" value every month

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

Highlighted
Contributor III
Contributor III

Re: How to add "-2" value every month

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: How to add "-2" value every month

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

- Marcus