Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all-
I've read through lots of questions and blog posts regarding how to show select/show data based on a time frame but I'm still coming up short...
I would like to show my dashboard to show the most current month (which happens to be last month, April). I've played around with the Master Calendar, Set Expressions and Variables and can't get it to calculate right...
I'm trying to pull the Responsible $ by the Date of Incident and have it show the most current month (April 2016).
I've tried using the Set Analysis Wizard and have tried a combination of options and can't get it to calculate. I'm wondering if the issue lies in how the Master Calendar is set-up // connected to my data? Any helps is appreciated, getting frustrated with my limited experience in scripting...
Master Calendar
Load
TempDate AS [Date of Incident],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
Day(TempDate) & '-' & Month(TempDate) as DayMonth,
WeekDay(TempDate) as WeekDay;
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
LOAD
min(FieldValue('Date of Incident', recno()))-1 as mindate,
max(FieldValue('Date of Incident', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date of Incident');
LET vLYTD = Date(MakeDate(Year(Today())-1 , Month(Today()) , Day(Today())),'YYYY-MM-DD') ;
LET vCQTD = Date(Floor(QuarterEnd(Today())) , 'YYYY-MM-DD') ;
LET vLQTD = MakeDate( Year(QuarterEnd(Today(),-1)) , Month(QuarterEnd(Today(),-1)) -1 , day(Today())) ;
Data Table
[Rework Data]:
LOAD
2016 as [Rework Year],
"DATE" as [Date of Incident],
"Original Ticket Foundation" AS [Original Job #],
"Rework Ticket Foundation" AS [Rework Job #],
CUSTOMER AS [Client],
"PROBLEM DESCRIPTION" AS [Description],
"Corrective Action Status",
"Resp Area" AS [Responsible Area],
"% Resp",
"Resp Area (adj)",
Pieces,
"% Qty",
"Qty (adj)" AS [Responsible Qty],
"$ REWORK",
"% $",
"$ Rework (adj)" AS [Responsible $ ]
FROM [lib://Rework 2016 (iwco_mlkish)]
(ooxml, embedded labels, table is [Calc Data]);
How about this:
Sum({<Month = {"$(=MaxString({<Year = {$(=Max(Year))}>}Month))"}>}[Responsible $ ])
Alternatively, you can try this as well
Sum({<[Date of Incident] = {"$(='>=' & MonthStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident])))"}>}[Responsible $ ])
How about this:
Sum({<Month = {"$(=MaxString({<Year = {$(=Max(Year))}>}Month))"}>}[Responsible $ ])
Alternatively, you can try this as well
Sum({<[Date of Incident] = {"$(='>=' & MonthStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident])))"}>}[Responsible $ ])
The second solution worked! (the first one did not, it brought in many months...)
Thank you so much for your help and timely response! Can you help me understand the logic behind the second string?
Also - how would I modify the second solution to pull in Last YTD data?
Last year YTD? or this year YTD?
For this year:
Sum({<[Date of Incident] = {"$(='>=' & YearStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident])))"}>}[Responsible $ ])
For last year YTD:
Sum({<[Date of Incident] = {"$(='>=' & YearStart(Max([Date of Incident]), -1) & '<=' & MonthEnd(AddYears(Max([Date of Incident]), -1)))"}>}[Responsible $ ])
I am just the expression to filter the dates between MonthStart for the max date and MonthEnd. To see the date range, you can add this to a text box object:
='>=' & MonthStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident]))
and you will see that if the max date is May 15th, 2016, the above expression will show a range of May1st to May 30th. And since this range is specified in the date field, we see only Month to Date sum