Create a dynamic Reference line Based on Cycle group (Week - Month)

[lessassy]

Hello everyone,

I have a bar chart which represents values based on weeks and month (i used a cycle group for the filter)

And basically my target are

2017

Month: 200

Week: 50

2018

Month: 150

Week: 65

And each year It's changing.

I'm using a master calendar

MasterCalendar: 

Load 

               TempDate as Created,

               Year(TempDate) As Year,

               week(TempDate) As Week,

               WeekYear(TempDate) & '-' & Num(Week(weekstart(TempDate)),'00') as WeekYear,

               Month(TempDate) As Month,               

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

I'm not very good with date formula so anyone could tell me if there is an appropriate formula for this ?

Thank you

[ogautier62]

Hi,

maybe add a new table in script with field :

Year, budgetMonth,BudgetWeek

then you can add theese two expression in chart as a line

cdt

[lessassy]

I've never added a new table with field name.

And besides, I don't have any field named Year nor Target .

The year field is created by the master calendar.

SO i don't know if your solution could work.

But as i said i don't know what you mean by added another table.

[lessassy]

With which function can I add these two target.

I don't know what are the proper formula to include that in the line expression. could ypu please share the information with me.

thank you

[ogautier62]

ok,

so you can add a new expression in chart as a line with LBU

[shruthibk]

monthname() gives 'Jan 2018' format