Last n values in a line chart

[brf10043]

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

[vamsee]

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]

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]

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]

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]

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

[brf10043]

Here is a sample file to refer to.

[vamsee · Accepted solution]

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]

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]

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)