topic Re: Set Analysis anomaly when plotted in QlikView

Set Analysis anomaly when plotted

alexis — Thu, 19 Dec 2013 17:57:46 GMT

Below is a screen shot of the anomaly I will try to describe.

I am performing some calculations which I will simply call "Numerator", "Denominator" and a ratio of the two

When I select a month/year, the 3 text boxes (see arrows) show the correct figure for the numerator (51554), denominator(62340) and the ratio (82.7%). In a table of the same figures however, the values for denominator and ratio (by definition) are incorrect (see red figures). Oddly enough though the Expression total (see BLUE rings) is correct.

If I now deselect the month, the table looks as follows - all the numerator figures are still correct and all the denominator ones are not - the November Denominator that we saw earlier is even different to before, so I am totally buffled.

Here are the expressions:

Numerator: = count ({$<[POSStatusID] = {'21','22'}>} DISTINCT TID)

Denominator:

= sum({$<[$(=Concat({1<$Field-={'VendorName','ModelName'}>}distinct $Field,']=,[')&']='),

[ProductionEnd] = {'>=$(=Min(DateNo))'},

[ProductionStart] = {'<=$(=Max(DateNo))'} >} TransRecNo)

The Blue part of the denominator builds a list of fields that are to be ignored for all fields except those listed (e.g. sum({$<[field1]=, [field2]=, ....

The Green part basically compares 2 fields in my Transactions table "ProductionStart" and "ProductionEnd" with the date range for each month

It would be hugely appreciated if someone has any suggestions regarding this odd behaviour!

Alexis

Re: Set Analysis anomaly when plotted

Anonymous — Thu, 19 Dec 2013 18:25:53 GMT

Quite a logic...

I'm not trying to get into it, but the syntax is not clean, I think. Try this:

= sum({$<$(='[' & Concat({1<$Field-={'VendorName','ModelName'}>}distinct $Field,']=,[') &']='),
[ProductionEnd] = {'>=$(=Min(DateNo))'},
[ProductionStart] = {'<=$(=Max(DateNo))'} >} TransRecNo)

Re: Set Analysis anomaly when plotted

Anonymous — Thu, 19 Dec 2013 18:36:48 GMT

On the second thought - why don't you use this:

= sum({1<[ProductionEnd]={'>=$(=Min(DateNo))'}, [ProductionStart]={'<=$(=Max(DateNo))'} >} TransRecNo)

Re: Set Analysis anomaly when plotted

alexis — Thu, 19 Dec 2013 18:57:37 GMT

Thanks for responding.

The code that you "removed" is responsible for ignoring all field selections except the 2 listed (VendorName and ModelName). This code was picked up from a wonderfully helpful thread

http://community.qlik.com/docs/DOC-1334

In any case, the code works as was shown in the text boxes in my description. It is the behaviour in a plot (graph, table etc) which is puzzling!

Thanks

Alexis

Re: Set Analysis anomaly when plotted

Anonymous — Thu, 19 Dec 2013 19:14:39 GMT

Alexis,

I didn't "remove" it - notice that I replaced "$" with "1". That is, instead of rule

"ignore fields <listing all fields here> but use ProductionEnd and ProductionStart"

I use the rule

"ignore all fields but use ProductionEnd and ProductionStart"

As for the specific problem - it would be helpful if you upload the app.

Regards,

Michael

Re: Set Analysis anomaly when plotted

Gysbert_Wassenaar — Thu, 19 Dec 2013 19:14:48 GMT

A set analysis expression calculates one set for the entire chart, not a set per row. That's probably why the totals are correct, because those use the complete data set. It would also be the reason that this part will probably not select the ProductionEnd and ProductionStart dates you expect:

[ProductionEnd] = {'>=$(=Min(DateNo))'},

[ProductionStart] = {'<=$(=Max(DateNo))'}

Re: Set Analysis anomaly when plotted

Anonymous — Thu, 19 Dec 2013 19:19:03 GMT

This is the most probable reason. It worth trying "if" instead of "set", given that the month-year is a dimension.

Re: Set Analysis anomaly when plotted

alexis — Thu, 19 Dec 2013 21:44:34 GMT

I'll try that Michael thanks!