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.

[sunny_talwar]

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

[Chanty4u]

hi

Excluding values in Set Analysis

[#4]

Hi Tony,

did you try

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

so just without the =?

[Oleg_Troyansky]

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

[jagan]

Hi,

Remove = in report date value

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

Regards,

Jagan.

[sunny_talwar]

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

[Peter_Cammaert]

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

[Oleg_Troyansky]

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

[#10]

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