Set Analysis....Syntax for using Date

[#1]

In the following expression my date as seen below is completely being ignored.  More specifically, I want the data as of 03/31/2016 and not what the user has selected in the filter pane.

The expression below returns the data for the "Child Support" name but just ignores my date completely and uses the date in the filter pane.

Any assistance would be greatly appreciated.

[Anonymous]

Hi

max({1<[Report Date ]={"=Date(03/31/2016,'MM/DD/YYYY')"},[Name]={"Child Support"}>}[Percentage Complete])

Regards.

[sunny_talwar]

I am amazed that double quotes around the date format did not give you an error and actually worked. I would have thought that this would error out the expression. Not sure if this is a change from QlikView.

[johnw]

I think an understanding of how it works lets us make an educated guess on why it works that way. I suspect that mimicking list boxes to trigger the engine to build the set was simply the easiest and most straightforward way for them to implement this sort of thing, with the most reuse of the existing code base. It gave them very powerful features for probably very little effort, at least compared to trying to code something more custom.

I knew that set analysis could do advanced search just like a list box could, yet never thought to use this fact to do numeric rather than text comparisons on dates and numbers, so thank you, Oleg!

[swuehl]

As far as I understood Henric in a different discussion, Qlik is well aware that there is potential for improvement here (mismatching format of field modifier element sets is probably one of the biggest issues for SA starters, at least when looking at the discussions here in the forum).

There is also a small penalty in using advanced search in a field modifier, since it's creating a hypercube in memory.

edit: And because it's building a hypercube, I think the current selection will be considered, so there might be a difference in returned sets between the above alternative field modifiers.

So IMHO, creating a better (i.e. at least numeric representation based) value matching would probably be best.

[johnw]

Yeah, better value matching would be nice. I'm a little surprised it's still the way it is, but it may require a fairly significant effort, and it does work as is, so I can see other things taking priority all this time.

[Peter_Cammaert]

Sunny, there are reasons why QlikView acts like this in some situations, and acts differently in other circumstances. We may long for a tool that consistently applies a theoretical ruleset to all situations in the same way. But in the end; QlikView is a product of this human world, and not a product of a virtual/imaginary world where theory is reality in its own right. With the additional disadvantage that theory automatically comes with much less flexibility as we would like to have in our dreams (and expectations) of an ideal world.

The QlikView developers often have to match their own ideals with reality and this leads to (a lot of) compromises or trade-offs. Or, to put it differently, "we don't live in a perfect world", so neither should software applications. The stuff that we are, as end-users, not necessarily happy with, is often the output of some design conflict where benefits of a particular choice are in perfect balance with the costs or disavantages. The choices they make are something we can easily live with to get our targets realised. "Nothing should be too simple to learn, or you'll be creating a new kind of slaves..."

Our best ally to shed light on questions like this is - of course - Henric, The one that is both on the inside and on the outside of our beloved toolkit and who is at the same time (all hail to the heavens) extremely well versed in explaining complex QlikView stuff (and some nuclear things too) to all community members. Since R&D will never participate in Qlik Community to defend their design decisions or anything else (their good right and do not question this. This is the right way, I assure you), I think he is our most valuable asset to explain the reasoning behind for instance: The Expression Search

P.

[Saravanan_Desingh]

Hi troyansky‌

Just a question on Advanced Search.

Concat({1<Fish={"=Ocean='Atlantic' And Fish Like 'B*'"}>} Fish,',') // Advanced Search

Concat({1<Ocean='Atlantic', Fish={"=Fish Like 'B*'"}>} Fish,',') // Not Working

The second example is not working. It means that 'Advanced Search' can not be combined with other Dims?

Can you please advise? Thanks.

[swuehl]

The syntax of the second expression is not correct, it should look like

Concat({1<Ocean= {'Atlantic'}, Fish={"=Fish Like 'B*'"}>} Fish,',')

The two expressions might return different results, though.

The first expressions will return fish where for the respective fish, =Only(Ocean) = 'Atlantic'  returns true, i.e. it appears only in atlantic ocean, while the second expression only requires the fish to show a relation to 'Atlantic', but the fish could appear additionally in other oceans, too.

[Saravanan_Desingh]

Thank you for your response swuehl‌

My bad. I have not seen the syntax properly. Nice explanation.

[#11]

its amazing how you can learn something like this here in the community.  Thank you! it worked nicely for me!