Hi Community

Otavio_Ads

Hello, I'm trying to create a line chart by week and average number of days. Using the `week` (field), I can get the weeks of the year (from 1 to 52), but I want to create a line chart that shows only the current month and its four weeks (1, 2, 3, 4, and 5).

I've tested some scripts, but none worked because they consider the average of the week numbers from previous years.

Does anyone have any idea how to solve this, either in the data loading script or in the chart itself?

BIAKS

Hello,
If you are using Avg(Days) as the expression in the line chart, you can do something like this to get only this year's values:

Avg( {<Year={"$(=Year(Today()))"}>} Days)

Also, if you want to show only this month's values you can do it with:

Avg({<Year={"$(=Year(Today()))"}, Month={"$(=Month(Today()))"}>} Days)

This fields are given considered that you have a masterCalendar table joined on CalendarDate with your Fact Data's Date field:

Let vMinDate = Num(Date(Floor(YearStart(AddYears(Today(),-2))),'DD.MM.YYYY'))-1;
Let vMaxDate = Num(Date(Floor(YearEnd(AddYears(Today(),1))),'DD.MM.YYYY'));
SET vHolidays = '01.01.2026','14.02.2026'; // Holidays etc.
Let vToday = Num(Today());
//Autogenerate a source table for your calendar
MasterCalendar:
NoConcatenate
Load
    date(TempDate,'DD.MM.YYYY') as CalendarDate,
    Week(TempDate) as Week,
    Year(TempDate) as Year,
    Month(TempDate) as Month,
    Day(TempDate) as Day,
    WeekDay(TempDate) as WeekDayName,
    Date(TempDate)-YearStart(TempDate)+1 as DayNumber,
    Num(WeekDay(TempDate)+1) as WeekDay,
    'Q' & ceil(Month(TempDate)/3) as Quarter,
    Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,
    Week(TempDate) & '-' & Year(TempDate) as WeekYear,
    inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,
    inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,
    NetworkDays(TempDate,TempDate,$(vHolidays)) as IsWorkingDay
;
Load																	                                                      
    $(vMinDate) + (IterNo()) as TempDate					    
AutoGenerate 1										
While 												
    $(vMinDate) + (IterNo()) <= $(#vMaxDate)
;

Hope this helps.

Otavio_Ads

Hi! 

I received an insight from a developer here at my company and was able to solve the problem even before testing the solution. 

Even so, tks.

Data Transformations

Upsolver