topic 11.2 R4 Bug in Set Analysis in QlikView

11.2 R4 Bug in Set Analysis

Anonymous — Mon, 21 Oct 2013 19:16:49 GMT

I upgraded to 11.2 R4 from R2 on 10/18. It appears as though there may be a bug in the way they applied one of the updates noted in the release notes stating that ',' is now a special wildcard character under Bug fixes and other Minor Improvements.

Bug fixes and Other Minor Improvements

"Treat single '*', '?', '<', '>' and '=' as Special Wildcard Characters when search

in list box, as well as in a Calculated Dimension, to avoid confusions with the

Wildcard search syntax."

I have a reporting.qvw which we use to distribute excel reports to our managers. Each chart has several different set analysis expressions to calculate fields in the report.

This particular expression

-sum( {$<FinancialMarkersTransGL= {'4','5'} ,fiscal_yr={$(vYear)},prd_of_year={$(vPTD)},day_of_prd ={">=$(=$(FirstDay))<=$(=$(MaxDay))"} >} PostAmt )

In this particular example for the FinancialMarkersTransGL, instead of only summing up Markers 4 and 5, it is now currently summing up markers 4, 5, 57 and 58. 57 and 58 being the only other markers that begin with a 5 that have activity b/w the firstday and maxday. 4 and 5 are the net sales Markers(these are debits), while 57 and 58 are actually asset markers(these are credits).

I have actually downgraded back to Desktop R2 to resolve this problem. Has anyone else has encountered something like this particular incident in the latest Release?

I have submitted this to support, they requested I submit the QVW for review, but actually the QVW is over 700MBs so I can't exactly send it anywhere. I will however show the a couple images of the particular column that has been directly affected. These percentages should be in the 28 to 35 range for Cost of Sales.

First column is the bugged calculation after the upgrade. The second column is the accurate calculation after downgrading back to the previous version.

Edit: added reduced version of the file. The COS tab is the tab that the images above come from. The attached file is also from after I downgraded to R2.

Edit2: Removed attachment

Re: 11.2 R4 Bug in Set Analysis

Gysbert_Wassenaar — Tue, 22 Oct 2013 06:19:29 GMT

You can use the Reduce option to trim down your 700MB file to something a lot smaller. See this document: Preparing examples for Upload - Reduction and Data Scrambling

Have you tried FinancialMarkersTransGL={4,5}?

Re: 11.2 R4 Bug in Set Analysis

Anonymous — Tue, 22 Oct 2013 15:55:38 GMT

I did try removing the single quotes and the calculation still pulled in marker 57 and 58. I went ahead and attached a reduced version of the reporting file I have. QlikTech go ahead and grab this file if you like. If you create a new chart using StoreNo and FinancialMarkersTransGL for the dimensions, and use this formula quoted below, you will see 57 and 58 are somehow part of the equation in R4.

-sum( {$<FinancialMarkersTransGL= {'4','5'} ,fiscal_yr={$(vYear)},prd_of_year={$(vPTD)},day_of_prd ={">=$(=$(FirstDay))<=$(=$(MaxDay))"} >} PostAmt )

I started out trouble shooting by assuming the base data wasn't entered into our system correctly so I pretty much followed the data trail from the beginning back to Qlikview, before I realized what it was doing.

Re: 11.2 R4 Bug in Set Analysis

Carlos_Reyes — Tue, 22 Oct 2013 16:59:35 GMT

I have the exact same problem. I created a small example to show what I mean.

My problem is that I'm very used to filter MonthYear periods with Set Analysis. This is what I usually do:

sum ( {$<MonthYear={ ' $(= MonthName(Max(Date))) ' }>} Quantity)

sum( {$<MonthYear={$(=CHR(39)&MonthName(Max(Date))&CHR(39))}>}  Quantity)

sum( {$<MonthYear={'Oct 2013'}>} Quantity)  //I only tried to type it manually to discard a problem with the formulas

But none of these work.

I've only been able to get around this problem with the >= and <= wildcards.

sum( {$<MonthYear={">=$(#=MonthName(Max(Date)))"}>} Quantity)

However, and due to this bug now I can't use Set Analysis inside SetAnalysis.

I think it is indeed a bug since I found this problem just after upgrading to SR4

Re: 11.2 R4 Bug in Set Analysis

Gysbert_Wassenaar — Tue, 22 Oct 2013 17:26:28 GMT

I think you found a subtle little bug. There is no FinancialMarkersTransGL value 5 in your data (*). It seems like if QV cannot find an exact match it will go for a wildcard match regardless of the fact that the user specified an exact match, i.e. '5'.

There are some workarounds like

making sure a value 5 does exist in the data
a search string like '=FinancialMarkersTransGL=4 or FinancialMarkersTransGL=5'
in this specific case a search string '>=4<=5' instead of '4','5'

(*) CatDesTransGL value Premium_Sales is missing its FinancialMarkersTransGL value. Should that be 5?

Re: 11.2 R4 Bug in Set Analysis

Carlos_Reyes — Tue, 22 Oct 2013 20:17:12 GMT

Ok,

So this is what QlikTech says about my "bug":

In SR4, you have to explicitly tell set analysis when you want a perfect search so instead of doing 'F' for a value. You need to put ['F'] the brackets tell qlikview to interpret literally the expression inside of it which will then pass along the single quotes b/c SR4 doesn't pass along the single quotes if they are by themselves now.

Basically, don’t use ‘f’, use [‘f’] for perfect searches.

So... now I have to add another character to my Set Analysis formulas.. It's getting more and more complex with the time.

Re: 11.2 R4 Bug in Set Analysis

Anonymous — Tue, 22 Oct 2013 20:31:34 GMT

Thanks, G

Yes it should be 5, actually. I had forgot to add it back when I was trying to trouble shoot Friday. Thanks.

Carlos, that is quite a strange change. I had in fact removed the single quotes from my Set Analysis and still got extra data I wasn't looking for. That change is actually affects a large portion of my Qvws already developed. Many of my charts for reporting are setup with multiple values in the Set analysis.

Edit: Wait, this means my values would need to look like this? {['4'],['5']}.. I'll test it out when I have some time later this week.

Re: 11.2 R4 Bug in Set Analysis

Carlos_Reyes — Tue, 22 Oct 2013 20:41:21 GMT

I know! It took me a lot of time to figure out that something was wrong with my Set Analysis... It was something I had mastered and became a standard in all my apps when using Rolling Periods. Now all my apps, including previous customers, will be affected by this change. I already rolled back to SR3 and I don't think I'll update to SR4 in a long time.

Re: 11.2 R4 Bug in Set Analysis

Gysbert_Wassenaar — Wed, 23 Oct 2013 05:37:36 GMT

Wow, that's an incredible bad design decision imho.... if it's true. It's going to break tons of apps and makes things even more complicated to understand. This is going to cost people large amounts of time trying to troubleshoot their documents.

Edit: I still think this only occurs if the value that is being matched doesn't exist. See attached example.

Product A has Class values 4, 42 and 44. The value 4 is matched exactly. Product B has Class values 51, 52, 58 and 59, but not a value 5. In this case the value 5 cannot be matched exactly and the match is made instead on 51, 52, 58 and 59. The square brackets correct this. In my opinion this behaviour is a bug, possible defective by design. It's is totally not what a user would expect.

From the release notes of SR4:

A number of fixes to ensure a more consistent and correct behavior for Select and Search.

New Features:

Perfect Match: now if you do a Perfect Match using a search string embraced by single quote/apostrophe ('), you'll get exactly what you want.

Except in set analysis expressions, where we did have that and now no longer do ...

Re: 11.2 R4 Bug in Set Analysis

tresB — Wed, 23 Oct 2013 05:43:34 GMT

It's really surprising !! Now, I am certainly not going to use SR4, and hope Qliktech would take care of this in newer versions.

Re: 11.2 R4 Bug in Set Analysis

rbecher — Wed, 23 Oct 2013 09:00:51 GMT

Can you please move this discussion into the QlikBug group: QlikBug

Re: 11.2 R4 Bug in Set Analysis

advait_thakur — Wed, 23 Oct 2013 09:38:24 GMT

This is a very surprising behaviour. Thank you Rodrick for discovering & opening the eyes of others. There must be something to come up with this. If users/customers have upgraded to SR4 then it might hamper many applications, identifying the root cause will be more challenging.

Re: 11.2 R4 Bug in Set Analysis

gussfish — Wed, 23 Oct 2013 21:55:25 GMT

I completely agree with you, G Wassenaar, that's a shocking design decision, a massive break in backward compatibility. An error in judgement like that make me wonder if QlikView is now complex enough that it's becoming difficult for QlikTech to evaluate changes; and that QlikTech should start crowdsourcing their design reviews in the QlikView Community so that they get a more complete impact analysis and depth/roundedness of solution.

Angus.

Re: 11.2 R4 Bug in Set Analysis

rbecher — Wed, 23 Oct 2013 22:03:43 GMT

Now v11.2 SR4 has been withdrawn, that's what I've expected after this issue was posted:

http://community.qlik.com/blogs/technicalbulletin/2013/10/23/qlikview-1120-sr4-update

I think this set analysis issue has to be fixed..

Re: 11.2 R4 Bug in Set Analysis

Gysbert_Wassenaar — Fri, 25 Oct 2013 17:32:04 GMT

Looks like this issue was indeed the reason to redo SR4: http://community.qlik.com/blogs/technicalbulletin/2013/10/23/qlikview-1120-sr4-update#comment-10306

Re: 11.2 R4 Bug in Set Analysis

Gysbert_Wassenaar — Fri, 01 Nov 2013 08:20:57 GMT

This issue is fixed in the newly re-released SR4.