multiple years in line chart with range selection

[#1]

Hello community,

In a line chart I want to display the trend over multiple years plus a range selection the user can set.

I have to distinguish between CalendarWeek and CalendarMonth. They are mutually exclusive so I show/hide them.

I've concatenated Year &'-'& CalendarWeek AS YearWeek and Year &'-'& CalendarMonth AS YearMonth but  this doesn't work with the variable I use to select the range.

SUM({<CalendarWeek={">=$(=Max(CalendarWeek)- ($(vSetCalendarRange)-1))<=$(=Max(CalendarWeek))"}>} Amount)

Of course Max(YearWeek) doesn't return a value.

What can I do to fix this so that the range selection works over more than one year?

Thank you,

Thorsten

[sunilkumarqv]

Firstly create a CalenderWeek in script like Wee(date) as CalendarWeek then used expession like this

Sum({<CalendarWeek={"<=$(=max(CalendarWeek))>=$(=max(CalendarWeek)-($(vSetCalendarRange)-1))"}},Qtr=>}Amount))

Hope you got somthing

[#3]

@sunil: this looks like what I already have.

[#4]

I assume you concatenated like..

Week(TempDate)&'-'&Year(TempDate)

[Michiel_QV_Fan]

Do you have a master calendar in use?

When you use a master calendar and you create a slider variable for the desired master calendar object your selection will show the range you set. Then you do not even need your set analysis. The date range selected will calculate the sum(amount).

[#6]

I do not have a master calendar in use. I already get the week, month and year numbers from the db-table.

Can I build a master calendar based on those three fields or is there a simpler approach I could try?

[Michiel_QV_Fan · Accepted solution]

Build a master caledner on the deepest date you use. From your fact table build a master calender on the date.

If you want to build a master calender with the mentioned fields you can use the makedate function.

makedate(year, month,day). If you do not give the field for day, 1 is presumed and you will get a date: 2014-02-01 for february 1.

Link your master calender to this date.

On this forum are many examples to be found for master calendars.