Date Format issue in Set Analysis

[#1]

Hi,

I've read a number of postings related to the subtletes of date formatting in Set analysis, however, I'm unable to figure out whether what I'm trying to do is valid.

Is it possible to format your field in a set analysis to then compare to the expression? Essentially I have a field that contains the timestamp that I'm trying to get rid of in order to compare in rounded days, weeks, months etc. Below is an example:

My field [Order.Add Date] contains a timestamp that I'd like to strip off in order to compare to the max[Order.Receive Date] which does NOT have a timestamp. Is this achievable with some type of field formatting? I've used no quotes which throws a "bad field" error, single quotes which works but I believe is comparing the literal text value Date([Order.Add Date]).

Any suggestions would be greatly appreciated.

Sum({$<'Date([Order.Add Date])' ={$(=Date(Max([Order.Receive Date])))}>} [Order.Request Value])

Thanks,

Dan

[vgutkovsky]

Dan,

I would suggest formatting it in the script rather than in a set analysis expression. You can use something like the following to create a separate field that contains just the date: date(timestamp#([Order.Add Date],'MM/DD/YYYY hh:mm')) as [Order.Add Date2]. You would of course modify this to fit your timestamp format. Then in set analysis you can easily compare dates without worrying about timestamp transformations.

Regards,

[#3]

Is it possible to do in the set analysis as opposed to the scripts?

[jonathandienst]

My understanding is that the LHS of a set modifier expression must be a field, not an expression, so I don't think you can do it that way.

You could try to modify the RHS expression to match the field format of the LHS, or else do it in script as Vlad suggested.

Jonathan

[Miguel_Angel_Baeyens]

Hi,

Although I think that Vlad's solution is better, you can actually check that on your set analysis, with something like

Sum({$<[Order.Add Date] ={"=Date([Order.Add Date]) = Date(Max([Order.Receive Date]))"}>} [Order.Request Value])

Anyway, I find this kind of cumbersome and it would be easier to add a new field in the script.

Hope that helps

[#6]

Hi Miguel,

I find your last suggestion particularly intriguing (although it works, of course).

In your syntax, the set modifier (<...>) is matching the [Order.Add Date] field to a particular value described by:

{"=Date([Order.Add Date]) = Date(Max([Order.Receive Date]))"}

I understand that as this is enclosed in {" "}, it is performing a search for values.

What I don't quite understand is the way in which the [Order.Add Date] field is compared to the Max([Order.Receive Date]) within the element set {"..."}. How does this return a value that would match the 'unformatted' [Order.Add Date] field at the beginning of the set modifier?

I hope I've managed to get my point across, and would be most grateful if you could explain what's going on here.

Regards,

Alex

P.S. Thanks for many helpful posts on Qlikcommunity!

[Miguel_Angel_Baeyens]

Hello Alex,

Say you have the [Order.Add Date] with format "07-03-11" (DD-MM-YY), and that your regional settings make the Date() function to return a value like "3/7/2011" (M/D/YYYY). Thus, fields will never match, since the format is different.

Since you need the analysis to get working, you can compare a field itself formatted to any given value. So the expression between brackets would read "return all values for [Order.Add Date] formatted as a date with the maximum value, formatted as a date, of [Order.Receive Date]". You are not using any variable in this case, but all values from a different field, and both need some functions applied to them.

Another example would be counting all values in CompanyID where the first two characters of CompanyID are "ES":

Count({< CompanyID = {"=Left(CompanyID, 2) = 'ES'"} >} CompanyID)

Although this makes no sense since this does the same.

Count({< CompanyID = {"ES*"} >} CompanyID)

Hope this shed some light.