topic Re: Newbie question about dates in Set Analysis expressions in QlikView

Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 21:22:13 GMT

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

Re: Newbie question about dates in Set Analysis expressions

santharubban — Tue, 25 Feb 2014 21:33:38 GMT

can you try this,

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

Re: Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 21:55:57 GMT

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 🙂

Re: Newbie question about dates in Set Analysis expressions

jpapador — Tue, 25 Feb 2014 21:58:41 GMT

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)

Re: Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 22:16:31 GMT

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?

Re: Newbie question about dates in Set Analysis expressions

maxgro — Tue, 25 Feb 2014 22:21:10 GMT

try this

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

Re: Newbie question about dates in Set Analysis expressions

jpapador — Tue, 25 Feb 2014 22:25:01 GMT

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;

Re: Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 22:33:55 GMT

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.

Re: Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 22:35:12 GMT

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).

Re: Newbie question about dates in Set Analysis expressions

maxgro — Tue, 25 Feb 2014 22:37:49 GMT

if possible post your doc

Re: Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 22:38:32 GMT

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

Re: Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 22:42:58 GMT

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.

Re: Newbie question about dates in Set Analysis expressions

Tue, 25 Feb 2014 23:02:49 GMT

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 😉

Re: Re: Newbie question about dates in Set Analysis expressions

Wed, 26 Feb 2014 14:03:20 GMT

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.

Re: Re: Newbie question about dates in Set Analysis expressions

Anonymous — Wed, 26 Feb 2014 14:08:10 GMT

Richard, see if the attached is of any help

Regards,

Michael

Re: Re: Re: Newbie question about dates in Set Analysis expressions

Anonymous — Wed, 26 Feb 2014 14:16:19 GMT

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

See attached

Re: Re: Newbie question about dates in Set Analysis expressions

Wed, 26 Feb 2014 14:20:25 GMT

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!

Re: Re: Newbie question about dates in Set Analysis expressions

Anonymous — Wed, 26 Feb 2014 14:28:28 GMT

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"