topic multiple years in line chart with range selection in QlikView

multiple years in line chart with range selection

Wed, 26 Feb 2014 13:11:28 GMT

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

Re: multiple years in line chart with range selection

sunilkumarqv — Wed, 26 Feb 2014 13:28:33 GMT

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

Re: multiple years in line chart with range selection

Wed, 26 Feb 2014 13:36:42 GMT

@sunil: this looks like what I already have.

Re: multiple years in line chart with range selection

Wed, 26 Feb 2014 14:32:58 GMT

I assume you concatenated like..

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

Re: multiple years in line chart with range selection

Michiel_QV_Fan — Wed, 26 Feb 2014 14:40:37 GMT

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).

Re: multiple years in line chart with range selection

Thu, 27 Feb 2014 06:24:15 GMT

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?

Re: multiple years in line chart with range selection

Michiel_QV_Fan — Thu, 27 Feb 2014 13:10:36 GMT

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.