Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 >.
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])
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;
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.
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.
Unfortunately, this doesn't work. I'm getting a single data point for November instead of Sept, Oct and Nov.
Here is a sample file to refer to.
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 >.
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)
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)