topic Last n values in a line chart in QlikView

Last n values in a line chart

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

Team,

I have a line chart showing durations over weeks. In the data I have 6 month's work of data. I am attempting to create a chart that will show the last 4 weeks worth of data based on the max week in the data. but can't seem to work out how to write such a dimension. It would nice if the dimension limits would allow me to select last n values.

In my load I do have the following, but can't work out how to create the dimension to plot the last 3 or 6 weeks data

num(Week(Date1)) as WeekNum,
WeekStart(Date1) as Week_Commencing,

WeekStart(Max(Week_Commencing)) as Max_Week_Commencing,
WeekStart(Max(Week_Commencing)-21) as Minus_3_Week_Commencing,
WeekStart(Max(Week_Commencing)-42) as Minus_6_Week_Commencing

Re: Last n values in a line chart

vamsee — Mon, 19 Nov 2018 17:18:43 GMT

You can handle this in the expressions of the chart using the set analysis.

sum({<Date={">=$(=Weekstart(Max(Date)-21))"}>}[Net Sales])

sum({<Date={">=$(=Weekstart(Max(Date)-42))"}>}[Net Sales])

Re: Last n values in a line chart

brf10043 — Mon, 19 Nov 2018 18:48:07 GMT

Okay, so that worked perfectly. Thanks for that. However, now my customer has changed his mind and has decided that he's rather see the data by months. I've attempted to modify the code with no luck.

Here is what I have so far.

Count({<Year_Month={">=$(=Year_Month(Max(Date1)-3))"}>}[RTPA Ticket #])

and here is the code that I am using to create my calendar

Temp:
Load Min([Incident Date]) as MinDate,
Max([Incident Date]) as MaxDate
Resident KPI;

LET vCMMinDate = peek('MinDate',0,'Temp');
LET vCMMaxDate = peek('MaxDate','0','Temp');

LET vNoOfDays = $(vCMMaxDate) - $(vCMMinDate);

drop table Temp;
//drop table FactTable;

CALENDER_TEMP:
LOAD $(vCMMinDate)+RecNo() as Date1
AutoGenerate $(vNoOfDays);

CM_CALENDER:
LOAD
Date(Date1,'MM/DD/YYYY') as [Incident Date],

Date1,
Year(Date1) as Year,
num(Month(Date1)) as MonthNum,
num(Week(Date1)) as WeekNum,
Month(Date1) as Month,
Week(Date1) as Week,
Day(Date1) as Day,
MonthName(Date1) as Year_Month,
WeekYear(Date1) as Year_Week,
WeekStart(Date1) as Week_Commencing,
If(Date1 >= MonthStart(Date(AddMonths(Today(0),-12),'M/DD/YYYY')), 'Yes') as R12?
Resident CALENDER_TEMP;

DROP Table CALENDER_TEMP;

Concatenate
CM_CALENDER:
Load
MonthName(Max(Date1)) as Max_Month,
MonthName(Max(Date1)-3) as Minus_3_Months

Resident CM_CALENDER;

Re: Last n values in a line chart

vamsee — Mon, 19 Nov 2018 19:07:42 GMT

I see that you have used Year_month field to restrict data, which has month names.

Instead, use MonthNum

Count({<MonthNum={">=$(=NUM(Month(Max(Date1)-3)))"}>}[RTPA Ticket #])

Also, please note that this restricts only months. So, if you have multiple years of data then you might have to restrict years too.

Re: Last n values in a line chart

vamsee — Mon, 19 Nov 2018 19:10:27 GMT

From your script, I assume you are joining the Calendar table to fact based on [Incident Date].

I would suggest you use [Incident Date] instead of Date1 for faster performance.

Re: Last n values in a line chart

brf10043 — Mon, 19 Nov 2018 19:20:59 GMT

Unfortunately, this doesn't work. I'm getting a single data point for November instead of Sept, Oct and Nov.

Re: Last n values in a line chart

brf10043 — Mon, 19 Nov 2018 19:27:08 GMT

Here is a sample file to refer to.

Re: Last n values in a line chart

vamsee — Tue, 20 Nov 2018 19:27:26 GMT

My bad in the expression I have suggested I misplaced the braces. It was subtracting 3 days instead of 3 months.

Changed it to

Count({<MonthNum={">$(=NUM(Month(Max(Date1))-3))"}>}[RTPA Ticket #])

Also, as you said you wanted Sep, Oct & Nov, changed the >= to >.

Re: Last n values in a line chart

brf10043 — Tue, 20 Nov 2018 21:10:44 GMT

That worked perfectly. Thanks!

ANy idea how I could now apply that logic to this expression?

linest_m(total aggr(Count (Distinct [RTPA Ticket #]),Year_Month), Year_Month)*Year_Month + linest_b(total aggr(Count (Distinct [RTPA Ticket #]),Year_Month),Year_Month)

Re: Last n values in a line chart

vamsee — Tue, 20 Nov 2018 21:17:31 GMT

I haven't worked much on Linest functions, maybe try

linest_m(total aggr(Count (Distinct {<MonthNum={">$(=NUM(Month(Max(Date1))-3))"}>} [RTPA Ticket #]),Year_Month), Year_Month)*Year_Month + linest_b(total aggr(Count (Distinct {<MonthNum={">$(=NUM(Month(Max(Date1))-3))"}>}

[RTPA Ticket #]),Year_Month),Year_Month)