Newbie question about dates in Set Analysis expressions

[#1]

I have a dataset where the dates are stored as strings of the form “YYYY-MM mmm” (so, for example, 2013-01 Jan, 2013-02 Feb, etc.).

When I load these into QV, I also parse apart the components to create an actual Date field of the form “M/D/YYYY” (this matches the date format set in the QV script.

So the Months table in QV looks like

StatusMonth                      StatusDate

2013-01 Jan                      1/1/2013

2013-02 Feb                      2/1/2013

2013-03 Mar                      3/1/2013

Etc.

I’ve checked the StatusDate values and  they are coming across as Dates (i.e. they can be formatted as a number).

The StatusMonth field is selectable by the user and drives various charts in the upper half of my sheet.  But in the lower half of my sheet, I want additional charts that (1) ignore the StatusMonth selection and (2) show values over time for the previous
6 months (i.e. always show the last 6 calendar months regardless of the user's StatusMonth selection).

I’ve tried all kinds of manipulations to get a 6-month rolling view (based on the latest date loaded) in these bottom charts and I
just can’t seem to get the Set Analysis expressions to work.

I’ve tried =Sum({$<StatusMonth=, StatusDate={">$(=AddMonths(Max({1} StatusDate),-6))"}>} Value)

I’ve also tried all kinds of variations on this.  Anyone have an idea of why that wouldn’t work?

I know I'm probably making a newbie error here somewhere <sigh>.

Thanks in advance,

rpb

[#2]

SQL##f - SqlState: 37000, ErrorCode: 4294964194, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'If' in expression.

SQL SELECT M.*,

   DateValue(M.MonthNum & '/01/' & M.Year) As StatusDate,

   If(MakeDate(M.Year, M.MonthNum, 1) >= AddMonths(MonthStart(Today()), -6) And MakeDate(M.Year, M.MonthNum, 1)<=Today(), 1, 0) as Rolling6MonthFlag

FROM ( SELECT MonthID,

    StatusMonth,

    Left(StatusMonth, 4) As Year,

    Mid(StatusMonth, 6, 2) As MonthNum

   FROM tblMonths) As M

[#3]

It's , uh, rather large.

Let me see if I can't pare things back to a much smaller dataset than what I've got loaded right now.

[#4]

Ok, that was interesting.  I dumped a couple thousand records out of the Access DB into Excel so I could try to load up a slimmed down version of the app (reasonably small enough to upload).

In Excel, I created a StatusDate column with the formula =DATEVALUE(MID([StatusMonth], 6, 2) & "/01/" & LEFT([StatusMonth], 4)) i.e. same as the SQL used to convert the StatusMonth values to StatusDate when going against the Access DB.

Loaded that Excel file and ... voila, the Set Analysis expression worked as expected.

{$<StatusMonth=, StatusDate={">$(=AddMonths(max({1}StatusDate),-6 ))"}>}

So there is something going on with the Date Formats here somewhere.  Does the AddMonths() function tack on decimal values?  If that's the case, why didn't changing it to use Floor(AddMonths(Max({1} StatusDate), -6)) work?

I'm confused.  Tiime for a drink 😉

[#5]

Here is a scaled down version of the App.  The two charts at the top of the sheet will respond to date selections.  The ones at the bottom should be showing July 2013 through January 2014.

[Anonymous]

Richard, see if the attached is of any help

Regards,

Michael

[Anonymous · Accepted solution]

The problem is that the StatusDate in your app is no date, it is timestamp.

See attached

[#8]

Wow!  Not sure I'd have ever found that, Michael.  Thank you.

It does seem strange, though, that Dates, Timestamps, et al are stored internally as numbers.  You would think, then, that greater than / less than comparisons would work across them without having to worry about format.

Oh well, live and learn!

Thanks, Michael.  Much appreciated!

[Anonymous]

In many cases it is not a big deal, but set analysis is very sensitive to the date/time format.  Notice that I used date() in my example in set analysis, but had to replace it with timestamp() in your app.

How I figured out?  Just added a list box StatusDate, and saw all these "...12:00 AM"