Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
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"
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;
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))
Thanks for the reply edwin, will try it out the logic 🙂