11.2 R4 Bug in Set Analysis

[Anonymous]

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

[Gysbert_Wassenaar]

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}?

[Anonymous]

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.

[Carlos_Reyes]

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)

or

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

or

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

[Gysbert_Wassenaar]

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?

[Carlos_Reyes]

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.

[Anonymous]

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.

[Carlos_Reyes]

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.

[Gysbert_Wassenaar]

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

[tresesco]

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