topic Re: Range of Dates based on Month Year Selection in App Development

Range of Dates based on Month Year Selection

Prasad1318 — Wed, 01 Feb 2023 13:32:46 GMT

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.

Re: Range of Dates based on Month Year Selection

edwin — Thu, 02 Feb 2023 18:21:32 GMT

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

Re: Range of Dates based on Month Year Selection

edwin — Thu, 02 Feb 2023 16:12:51 GMT

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"

Re: Range of Dates based on Month Year Selection

edwin — Thu, 02 Feb 2023 18:14:20 GMT

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;

Re: Range of Dates based on Month Year Selection

edwin — Thu, 02 Feb 2023 18:20:00 GMT

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

Re: Range of Dates based on Month Year Selection

Prasad1318 — Tue, 07 Feb 2023 06:52:29 GMT

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