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

[santharubban]

can you try this,

=Sum({$<StatusMonth=, StatusDate={'>$(=AddMonths(Max({1} StatusDate),-6))'}>} Value)

[#3]

Tried swapping my double quotes for your single quotes and still no joy.  The only difference is that when I use the double quotes I get nothing.  When I use your Set expression, all I get is the latest month (which in this case happens to be '2014-01 Jan')  So it almost looks like the Max() is working but not the search expression.

Appreciate the try at it though 🙂

[jpapador]

You could try making a 6 month flag in the script.  So something like:

If(Date >= AddMonths(MonthStart(Today()), -6) And Date <=Today(), 1, 0) as Rolling6MonthFlag

Then your expression would look like:

Sum({$<StatusMonth=, Rolling6MonthFlag={1}>}Value)

[#5]

I'm still trying to get my feet wet with Script, so not exactly sure where to put that line.

Here is the relevent part of the script that loads the Months:

SELECT     M.*,

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

                                 StatusMonth,

                                 Left(StatusMonth, 4) As Year,

                                 Mid(StatusMonth, 6, 2) As MonthNum

              FROM tblMonths) As M;

So where would I put the IF statement you're suggesting?

[maxgro]

try this

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

[jpapador]

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;

[#8]

Ok, I'll give that a try, but ... not sure that long-term that's going to fly simply because of the reliance on the Today() function.  If I ever need to go back and reload history, then that flag will be set based on the reload date rather than the latest date loaded from the data file.

[#9]

Adding the Floor() function didn't change anything.  I still get nothing.

I've checked and the numerical value of all of the StatusDate entries are integers (i.e. no decimal time stamp).

[maxgro]

if possible post your doc