Range of Dates based on Month Year Selection

[Prasad1318]

Hi all,

I got a problem, please help me to solve it.

I have Fields DATE, Units. Using Date Field I am extracting Month_Year as 
Date(Monthstart(Date(Date#("Date", 'YYYYMMDD'))),'MMM-YYYY') as "Month_Year",

Now I have a table chart with Date as dimension

                                             and Sum(Units) as Measure.
and Month_year as Filter pane.

Generally when we select Dec-2022 in filter pane , we get filter dates of december in the Date Column of table.
But I need next month First Work day included  and first working day of selected month excluded 

For Eg : if we select Dec 2022, we need dates from 02/12/2022 till 30/12/2022 and also 03/01/2023, as 31/12/2022,01/01/2023,02/01/2023 are holidays.
and if we select Jan 2023 we need  dates from 04/01/2023 till 31/12/2023.

 In the Date column I will be getting only working dates.

Please help me on this issue.
 

[edwin]

you will need to build a bridge between your fact and your calendar.  reason being you are 1st changing what selecting a month means - DEC 2022 means Dec 2, 2022 to Jan 3, 2023, 2nd you also want to be able to select the next month - Jan 4, 2023 to Feb (whatever is your 1st working day for feb).  this also assumes you have a reference calendar that flags 1st working date + all working dates.
in your bridge you associate your month year (from your calendar) to all the working dates for current month and add a field MONTH_TYPE for example as "CURRENT" +
all the working dates for next month and in field MONTH_TYPE as "NEXT"

then in your set analysis add the modifier MONTH_TYPE = {'CURRENT'} for current month and MONTH_TYPE = {'NEXT'} for the next one.

as example, if you have in your fact table rows with dates for Dec 1, 2022 to Feb 28, 2023,
your bridge will look like this:

FactDate	CalendarMonthYear	MonthType
Dec 1, 2022	Nov 2022	Next
Dec 2, 2022	Dec 2022	Current
Dec 3, 2022	Dec 2022	Current
...	...	...
Jan 2, 2023	Dec 2022	Current
Jan 3, 2023	Dec 2022	Next
...	...	...
Feb 1, 2023	Dec 2022	Next
Jan 4, 2023	Jan 2023	Current
...
Feb 1, 2023	Jan 2023	Current
Feb 2, 2023	Jan 2023	Next

[edwin]

as you can see, when DEC 2023 is filtered, Dec 2, 2022 up to Feb 1, 2023 is selectable, but in your expression, you can partition/qualify these rows by adding the set analysis modifier MonthType = "Current" or "Next"

[edwin]

you of course already have a calendar, a list of work dates, and your fact and then you build your bridge.

following is a demo and i start with made up dates :

FactDates:
load *,
	(Date(MonthStart(FactDate),'MMM YYYY')) as FactMonthYear,
    (Date(AddMonths(MonthStart(FactDate),1),'MMM YYYY')) as FactNextMonthYear
    where FactDay<>'Sat' and FactDay<>'Sun';
load 
	date(today()-iterno()+30) as FactDate, 
    Text(WeekDay(date(today()-iterno()+30))) as FactDay
while iterno()<=1000;
load 1 AutoGenerate(1);


FirstDay:
load 
	FactMonthYear, FactNextMonthYear, 
    date(min(FactDate)) as FirstDay
Resident FactDates
group by FactMonthYear, FactNextMonthYear;

inner join (FirstDay)
load FactMonthYear, FactDate
Resident FactDates;

SecondDay:
load FactMonthYear,
	date(min(FactDate)) as SecondDay
Resident
	FirstDay
where FactDate>FirstDay
group by FactMonthYear;


NoConcatenate
FirstSecondDay:
load distinct FactMonthYear, FactNextMonthYear, FirstDay
Resident FirstDay;

inner join (FirstSecondDay)
load FactMonthYear, SecondDay as StartDate
resident SecondDay;

drop table SecondDay, FirstDay;

//at this point we have the following:
//FactMonthYear, FactNextMonthYear, FirstDay, SecondDay
//

inner join (FirstSecondDay)
load FactMonthYear as FactNextMonthYear, FirstDay as EndDate
Resident FirstSecondDay;

drop field FactMonthYear, FactNextMonthYear from FactDates;

left join (FirstSecondDay)
load FactMonthYear as FactNextMonthYear, StartDate as NextMonthStartDate, EndDate as NextMonthEndDate
Resident FirstSecondDay;

drop fields FirstDay;

inner join (FirstSecondDay)
load FactDate Resident FactDates;

Bridge:
load 
	FactMonthYear, StartDate, EndDate, FactDate, 'CURRENT' as MonthType
Resident
	FirstSecondDay
where 
	FactDate >= StartDate and FactDate<=EndDate;

concatenate(Bridge)
load 
	FactMonthYear, NextMonthStartDate as StartDate, NextMonthEndDate as EndDate, FactDate, 'NEXT' as MonthType
Resident
	FirstSecondDay
where 
	FactDate >= NextMonthStartDate and FactDate<=NextMonthEndDate;
    
drop table FirstSecondDay, FactDates;

Fact:
load FactDate, floor(rand()*100) as Measure
Resident
	Bridge;

Calendar:
load 
	FactMonthYear
Resident Bridge;

[edwin]

here is a sample result, my dummy date starts June 8, 2020, so June 2020 starts June 9, 2020 (2nd day)

Current Month =Sum({<MonthType={'CURRENT'}>}Measure)

Next Month = Sum({<MonthType={'NEXT'}>}Measure)
CurrentMonth StartDate =date(min({<MonthType={'CURRENT'}>}FactDate))
Current EndDate =date(max({<MonthType={'CURRENT'}>}FactDate))
NextMonthStartDate =date(min({<MonthType={'NEXT'}>}FactDate))
NextMonthEndDate=date(max({<MonthType={'NEXT'}>}FactDate))

[Prasad1318]

Thanks for the reply edwin, will try it out the logic 🙂