Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
brf10043
Creator
Creator

Last n values in a line chart

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

1 Solution

Accepted Solutions
vamsee
Specialist
Specialist

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

View solution in original post

9 Replies
vamsee
Specialist
Specialist

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

Like

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

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

brf10043
Creator
Creator
Author

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;

vamsee
Specialist
Specialist

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.

vamsee
Specialist
Specialist

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.

brf10043
Creator
Creator
Author

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

brf10043
Creator
Creator
Author

Here is a sample file to refer to.

vamsee
Specialist
Specialist

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

brf10043
Creator
Creator
Author

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)

vamsee
Specialist
Specialist

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)