topic Re: Set Analysis....Syntax for using Date in QlikView

Set Analysis....Syntax for using Date

Thu, 14 Apr 2016 15:24:31 GMT

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.

Re: Set Analysis....Syntax for using Date

sunny_talwar — Thu, 14 Apr 2016 16:07:19 GMT

Try this may be:

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

UPDATE: My bad, added the Y now

Re: Set Analysis....Syntax for using Date

Chanty4u — Thu, 14 Apr 2016 16:10:18 GMT

Excluding values in Set Analysis

Re: Set Analysis....Syntax for using Date

Thu, 14 Apr 2016 16:11:56 GMT

Hi Tony,

did you try

max({1<[Report Date] = {"03/31/2016"}>, ....}

so just without the =?

Re: Set Analysis....Syntax for using Date

Oleg_Troyansky — Thu, 14 Apr 2016 16:15:50 GMT

One more Y in your formula, Sunny 🙂

in order to be free from formatting issues, I usually use Advanced Search for the same condition. Instead of:

[Report Date]={'03/31/2016'}, which may or may not work, use:

[Report Date]={"= [Report Date]='03/31/2016' "}

This way, the condition is verified as any comparison condition, free from formatting issues.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

Re: Set Analysis....Syntax for using Date

jagan — Thu, 14 Apr 2016 16:23:18 GMT

Hi,

Remove = in report date value

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

Regards,

Jagan.

Re: Set Analysis....Syntax for using Date

sunny_talwar — Thu, 14 Apr 2016 16:32:03 GMT

I am curious to know why this may or may not work:

[Report Date]={'03/31/2016'}

and this def. will work?

[Report Date]={"= [Report Date]='03/31/2016' "}

Oleg‌‌, Peter‌, John‌, jagan : I guess my bigger question here is‌ why is set analysis so reliant on format. It would be so much easier if we can have any format on RHS match with any format on LHS as long as they both mean the same thing.

For instance if I have DD/MM/YYYY = Num('DD/MM/YYYY'), I would think that this should work. Is there a reason why it doesn't work today? Why is matching the formatting such an important part of making it work?

Your feedback would be greatly appreciated.

Best,

Sunny

Re: Set Analysis....Syntax for using Date

Peter_Cammaert — Thu, 14 Apr 2016 16:32:27 GMT

And Tony, the part "...as of.." in your original post may mean that the output should include all dates starting from 3/31/2016. In that case, you can use this variation on Sunny's corrected expression:

Max({1<[Report Date] = {">=$(=Date(MakeDate(2016, 3, 31), 'MM/DD/YYYY'))"},

Name = {'Child Support'}>} [Percentage Complete])

Re: Set Analysis....Syntax for using Date

Oleg_Troyansky — Thu, 14 Apr 2016 16:53:23 GMT

Hi Sunny,

keep in mind that I'm not the author of Set Analysis, so I can't explain "why" certain things work the way they do, but I can possibly interpret my own understanding.

Set Analysis filters mimic user selections in List Boxes. For Strings and Integers there is no difference, but for Dates and decimal numbers, formatting does play a critical role. The rule of thumb is - if you can make this selection in a List Box, then you can create the same filter in Set Analysis.

In a List Box, when dates are formatted as MM/DD/YYYY, you can only make selection using this format - you can't use date serial numbers like 42123, or different formats like YYYY-MM-DD. This is why the corresponding Set Analysis filter needs to be formulated the same way.

Now, Advanced Search is a different story. When you wrap your condition in a set of double quotes "" and add the equals sign in front of your condition, QlikView will verify this condition in the context of your field (as opposed to selecting certain distinct values in the previous case):

Date = {"=Date>vNumericDateValue"}

This comparison is happening similarly to the corresponding comparison in an IF statement:

SUM( IF(Date>vNumericDateValue, Sales) )

In this comparison, numbers can be compared with dual values. Notice that within the Advanced Search condition, you don't even need to use a $-sign expansion for your variables. Magical, isn't it?

As always, I encourage all the readers that wish to learn more about advanced QlikView techniques (including Set Analysis) to check out my new book QlikView Your Business and to join me at the Masters Summit for Qlik, where I teach Set Analysis, Advanced Aggregation, and Performance tuning - next to three other respected Qlik experts Rob Wunderlich, Barry Harmsen, and Bill Lay.

cheers,

Oleg Troyansky

Re: Set Analysis....Syntax for using Date

Thu, 14 Apr 2016 17:03:01 GMT

Sunny T Thank you again......I substituted the ' with " and it works.

Thank you Sunny T, Oleg, Peter, Chanty 4U, Christof Gruber abd Jagan mohan rao appala.......

Re: Set Analysis....Syntax for using Date

Anonymous — Thu, 14 Apr 2016 17:21:42 GMT

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

Regards.

Re: Set Analysis....Syntax for using Date

sunny_talwar — Thu, 14 Apr 2016 17:25:16 GMT

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.

Re: Set Analysis....Syntax for using Date

johnw — Thu, 14 Apr 2016 22:36:42 GMT

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!

Re: Set Analysis....Syntax for using Date

swuehl — Thu, 14 Apr 2016 22:52:06 GMT

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.

Re: Set Analysis....Syntax for using Date

johnw — Thu, 14 Apr 2016 23:02:07 GMT

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.

Re: Set Analysis....Syntax for using Date

Peter_Cammaert — Thu, 14 Apr 2016 23:04:21 GMT

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

Re: Set Analysis....Syntax for using Date

Saravanan_Desingh — Sun, 24 Apr 2016 13:56:39 GMT

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.

Re: Set Analysis....Syntax for using Date

swuehl — Sun, 24 Apr 2016 14:54:51 GMT

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.

Re: Set Analysis....Syntax for using Date

Saravanan_Desingh — Sun, 24 Apr 2016 16:15:51 GMT

Thank you for your response swuehl‌

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

Re: Set Analysis....Syntax for using Date

Mon, 05 Dec 2016 13:50:32 GMT

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