I'm trying to build set analysis for MTD last year. I have a Date field tied to a calendar in the data model, and the calendar has fields Year, Month, Day.
Today is July 3rd, 2013, and the script was refreshed at 2AM today. There were no new transactions yesterday, so I have no records with 07/02/2013 in the data table, but the calendar does.
I want to build a table that will count transactions in the current month thus far.
I also want to build a table that will count transactions up to this point in the month in the year prior. The script reloads after midnight (reloads on July 3), but I'd want to compare July 1st - July 2nd, 2013, with July 1st - July 2nd, 2012.
I'd also want the ability to select a prior Year/Month/Day, and compare MTD of that year with the year before.
For MTD I have:
Year={$(=max(Year))},
Month={$(=max(Month))},
Date={"<=$(=max(Date))"}
and for LMTD I have:
Year={$(=max(Year)-1)},
Month={$(=max(Month))},
Date={"<$(=addmonths(max(Date+1), -12))>=$(=addmonths(num(MonthName(max(Date))), -12))"}
In the calendar, I am only pulling dates < today(), so this solves the not wanting July 3rd problem (script reloaded at 2AM, but we don't want to compare MTD last year with only 2 hours into this day). This approach mostly works.. but year/month/day only exists if a transaction exists in the data table. If someone makes any kind of list box selection, and we only have data on July 1st, MTD and LMTD will only show to date July 1st, instead of to July 2nd.
I think I may need a calendar island table, with only dates before the script reload date? How do I sum transactions counts in an unrelated table with this?
Or am I going about this all wrong? Thank you!
EDIT: To provide a better example. Say my user wants to see all Ohio transacations as of May 25th, 2013, and as of May 25th, 2012. Let's say there were only 2 Ohio transactions.. 1 on May 15th, 2013, and the other on May 20th, 2012. They expect to click May 25, 2013, and Ohio, and see those 2 transactions (1 in each chart). However, with the expressions I built... if they click May 25th, 2013, Ohio will be greyed out (because there were none on May 25th, 2013).
