topic Re: Create a dynamic Reference line Based on Cycle group (Week - Month) in QlikView

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

lessassy — Wed, 25 Nov 2020 16:16:04 GMT

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

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

ogautier62 — Mon, 28 May 2018 13:36:56 GMT

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

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

lessassy — Mon, 28 May 2018 13:51:46 GMT

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.

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

lessassy — Mon, 28 May 2018 19:40:03 GMT

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

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

ogautier62 — Tue, 29 May 2018 10:35:11 GMT

ok,

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

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

shruthibk — Tue, 29 May 2018 10:40:49 GMT

monthname() gives 'Jan 2018' format