Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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

Hello everyone,

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

Cycle group target.PNG

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

5 Replies
Highlighted
Specialist II
Specialist II

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

Highlighted
Creator
Creator

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.

Highlighted
Creator
Creator

With which function can I add these two target.

Target Target Target.PNG

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

Highlighted
Specialist II
Specialist II

ok,

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

Highlighted
Creator
Creator

monthname() gives 'Jan 2018' format