topic Re: exclude sintax in set analysis in QlikView

exclude sintax in set analysis

Tue, 19 Jun 2012 07:15:26 GMT

hello,

i have the following expression that im using to calculate EOY forcast:

sum({$<QVYear=,QVYearHlf=,QVYearQtr=,QVYearMonth=,QVMonthName=,QVQtr=, Site=>Sales}

logic is to disable time dimensions from this object. later on i multiply it by number of month to end of year...

now, i have to objects, one displays total forcast for EOY,

and in the other, i want to relate to a spesific site. in that case i add Site={site name}.

now i want to add an object that will calculate total forcast excluding the site selected in the above expression. how?

Re: exclude sintax in set analysis

jonathandienst — Tue, 19 Jun 2012 07:26:09 GMT

To exclude a site, use Site-={'site name'}

Hope that helps

Jonathan

Re: exclude sintax in set analysis

Tue, 19 Jun 2012 07:31:13 GMT

jonathan

as far as i know this will include only the selected site.

im looking to exclude that site.

Re: exclude sintax in set analysis

jonathandienst — Tue, 19 Jun 2012 07:36:35 GMT

I think you missed the - sign before the =. That's what does the exclude.

Oh, and don't worry about the syntax error squiggly lines, its a syntax check bug.

Regards

Jonathan

Re: exclude sintax in set analysis

Tue, 19 Jun 2012 07:44:20 GMT

ok, this one works,

but it doesnt disable the site dimension from affecting the object...

i have a table box for Site dimension on that page and if i choose a site that was excluded in the expression, value displayed is '0',

unlike a situattion where i wright "QVYear=". in that case a selection made on that dimension will simply not effect the value displayed

Re: exclude sintax in set analysis

Tue, 19 Jun 2012 07:47:37 GMT

Hi,

Following may work:

sum({$<QVYear=,QVYearHlf=,QVYearQtr=,QVYearMonth=,QVMonthName=,QVQtr=, Site=e(Site)>} Sales)

Regards

http://quickdevtips.blogspot.com/

Re: exclude sintax in set analysis

Tue, 19 Jun 2012 08:03:26 GMT

hi bilge,

if you ment somthing like Site=(site name)

than its not working.

i know it has somthing to do with 'e' added before or somewhere in the expression but cant get to the right syntax

Re: exclude sintax in set analysis

jonathandienst — Tue, 19 Jun 2012 08:04:34 GMT

It looks like something else in your data model is associating the site with your metric. I suggest that you post your model so that we can look at it in a little more detail.

Regards

Jonathan

Re: exclude sintax in set analysis

Tue, 19 Jun 2012 08:17:03 GMT

Hi,

I meant something like in the attached file.

Though, I am not sure if it is what you are looking for.

Best regards,

http://quickdevtips.blogspot.com/

Re: exclude sintax in set analysis

Tue, 19 Jun 2012 08:34:30 GMT

bilge,

no, this is not what im looking for.

jonathan,

the full expression is:

round(sum({$<QVYear=,QVYearHlf=,QVYearQtr=,QVYearMonth=,QVMonthName=,QVQtr=, Site={AU}>}if(Flag_ComYR=1,Charge,0))+

sum({$<QVYear=,QVYearHlf=,QVYearQtr=,QVYearMonth=,QVMonthName=,QVQtr=, Site={AU}>}if(Flag_PMonth=1,Charge,0))*(12-

if(num(Day(today()),'00')>=10,

num(Month(Date(today())),'00')-2,

num(Month(Date(today())),'00')-3)))

this expression returns the EOY forcast for our site in australia.

now i want to add another object that will returne total EOY forcast for all sites excluding the one in australia.

in dimension site i have 6 different values.

Re: exclude sintax in set analysis

jonathandienst — Tue, 19 Jun 2012 19:15:10 GMT

Not quite the same as uploading your model , but looking at the expression in isolation I can't see any reason why replacing the Site={'AU'} with Site-={'AU'} (both instances) does not work. Perhaps you have null sites; -= will not return null values??

Regards

Jonathan

Re: exclude sintax in set analysis

Wed, 20 Jun 2012 07:14:42 GMT

hi jonathan,

as i said before, your solution does work.

but,

if we use an expression like {site=}, then a table box with that dimension will not effect your object. just like i used QVYear=,QVYearHlf=,....

but when using site-={au}, it work but if user uses the site table box and select a different site than AU, the object will display null instead of not being effected like with the other expression....

i hope it makes more sense now....

Re: exclude sintax in set analysis

jonathandienst — Wed, 20 Jun 2012 20:17:18 GMT

I think I understand what you are saying, although it is not the behaviour I would expect. Without access to your model and a more in depth analysis of the model, it is hard to explain the behaviour or to propose a solution.

Regards

Jonathan

Re: exclude sintax in set analysis

Thu, 21 Jun 2012 07:37:00 GMT

jonathan,

i uploaded a model.

in the model u have 3 gauges where two of them use set analysis to present a specific site and the middle one uses set analysis to exclude the first two sites....

Re: exclude sintax in set analysis

jonathandienst — Fri, 22 Jun 2012 10:40:20 GMT

I don't see your model. Reply to this post, go to "Use Advanced Editor" and then click browse (under Attach Files) to locate the file to upload and click Post Message to upload the file.

Regards

Jonathan

Re: exclude sintax in set analysis

Sun, 24 Jun 2012 06:56:03 GMT

well i added it to the initial discussion but here it is again....

Re: exclude sintax in set analysis

jonathandienst — Mon, 25 Jun 2012 17:40:32 GMT

Still not quite sure what you are trying to achieve. To show the expressions for IN/AU and for everything excluding IN/AU is not complex.

You have other exclusions in the expression and I dont know how these are affecting the results. Bear in mind that these exclusions will be cumulative - only records that pass all the exclusions will be considered.

Try to get the correct behaviour with a simple expression first, something like

sum({$<Site-={IN,AU}>} Charge)

Then, when you have the correct behaviour, expand the expression one step at a time. If it breaks, then at least you will know where the problem is.

Regards

Jonathan

Re: exclude sintax in set analysis

Tue, 26 Jun 2012 06:18:13 GMT

hi,

as i said before, expression is ok and working.

no break downs.

the issue was that if i exclude IN for example, when user selects IN through a table box (for a different object on that same page), the expression will return 0. and this behavior is not as expected (you said so as well).

i whould expect the expression that excludes IN to not react to a selection of IN.