topic Re: Set Analysis - second selection? in QlikView

Set Analysis - second selection?

Wed, 22 Aug 2012 07:41:24 GMT

Hi all,

I have following expression:

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}>} $(varGrossSales))

It returns me the full set of records of variable Gross Sales, with the modifier selected year -1.

Now I want to take into consideration the country code. If I select the country, the result set should also be changed.

Is it possible to insert a new modifier: consider in the selection the year-1 and e.g.the country code.

I have tried this, but this does not work:

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}>*countrycode} $(varGrossSales))

Thank you in advance.

Regards Christoph

Re: Set Analysis - second selection?

swuehl — Wed, 22 Aug 2012 08:02:46 GMT

Have you tried something like

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}, COUNTRYCODEFIELD = {$(=Only(COUNTRYCODEFIELD))} >} $(varGrossSales))

Re: Set Analysis - second selection?

Wed, 22 Aug 2012 08:19:38 GMT

Hi,

that is what I need in principle, but something is not working in this code.

Are you sure, it is possible with 2 selections?

Regards CH

Re: Set Analysis - second selection?

swuehl — Wed, 22 Aug 2012 08:31:57 GMT

2 selections in one field (COUNTRYCODEFIELD)? Then only does not work.

Try

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}, COUNTRYCODEFIELD = p() >} $(varGrossSales))

If this doesn't help you, could you upload a small sample file together with your expected outcome?

Re: Set Analysis - second selection?

gandalfgray — Wed, 22 Aug 2012 09:21:28 GMT

If you just want to change the selection of TRANSYEAR with your set expression, and not affect other selections (ie in COUNTRYCODEFIELD, or any other field) why not use:

=-SUM( {$<TRANSYEAR={$(=Only(TRANSYEAR)-1)}>} $(varGrossSales))

That is change the starting '1' to '$'.

Re: Set Analysis - second selection?

Wed, 22 Aug 2012 10:25:31 GMT

Hi,

you have the yellow expression, this reacts to every filter selection. OK. This is the normal case without set analysis.

The grey expression is the set analysis, this should display only the full year of the selected transaction year.

And now it should also include the selection when filtering the country code.

Thank you in advance.

Regards CH

Re: Set Analysis - second selection?

Wed, 22 Aug 2012 10:28:29 GMT

Hi GandalfGrey,

thank you, but this is not useful for me, the setanalysis expression should give me only the full year. With the change to $, I will get the complete selection.

Regards CH

Re: Set Analysis - second selection?

gandalfgray — Wed, 22 Aug 2012 11:25:17 GMT

Hi hasenclc,

I looked at your sample app.

The yellow expression is not without set expression, you have {$<TRANSYEAR={$(=Only(TRANSYEAR)-1)}>} there selecting previous year.

And as far as i can see it displays the full previous year (2010 if you selected 2011)

And when you select a country (DE for example), the yellow expression gives you the sales for 2010 for the selected country.

Isn't that what you want?

The grey expression does not follow your country selection since you have 1 before the set <>.

To me it looks like the yellow expr gives you what you want .

regards GG

Re: Set Analysis - second selection?

swuehl — Wed, 22 Aug 2012 11:33:03 GMT

Would be helpful if you could add what you expect to see when doing selections in fields (specifying both the selections and the expected outcome).

If you are using the expression I suggested above

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}, %COUNTRYID=p() >} $(varGrossSales))

then this should regard your selections in Country ID and TRANSYEAR (from the multi box), but if you select in any other field, like Day, Account or Division, this will not affect the outcome.

edit: Maybe you need to add the year restriction also to the p() function:

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}, %COUNTRYID=p({<TRANSYEAR={$(=Only(TRANSYEAR)-1)}>}) >} $(varGrossSales))

Re: Set Analysis - second selection?

Wed, 22 Aug 2012 11:35:02 GMT

Hi GandalfGrey,

sometimes I am confused , I must admit, but not in this case.

Ok, the yellow expression has a set analysis, but for my point of view, the result is the same as without the set analysis. And this is not what I want.

Perhaps I have to explain a little bit better.

If you select a year, then I get a result for the yellow and the grey one. Great.

But my goal is, if I select for example a day, the grey one should remain the same, because it should display the full year (for comparison reasons)

Now I want to select a specific country. The grey one doesn't react, but for my goal, this should now.

I hope, you are not confused now.
Regards CH

Re: Set Analysis - second selection?

Wed, 22 Aug 2012 11:58:15 GMT

Hi, yes you are right. We are very close tho the solution.

The suggested expression works to 90%, but somewhere there must be an error, because of the red underline (perhaps with the brackets. ) and a missing value for a customer

I have checked different combinations in the formula, but I didn't get it. I am sorry, but I am new to this.

Do you have an idea?

Re: Set Analysis - second selection?

swuehl — Wed, 22 Aug 2012 12:05:55 GMT

I believe the red underline is a bug in the syntax checker (with the empty p() function, you could use the equivalent p(%COUNTRYID) to get rid of the red underline).

But I updated the expression to include the restriction in TRANSYEAR also in the p() function, see above. This should resolve the missing value.

Re: Set Analysis - second selection?

gandalfgray — Wed, 22 Aug 2012 12:14:45 GMT

Hi again

There is more than one way to do it but

what I normally prefer to do is stuff like:

Sum( {$<dim1={whatever}, dim2=, dim3=>} amount)

That is I use the current selection, but sets dim1 to another value of my choice, and unsets any choices in dim2 and dim3

(You often need to use this technique when dealing with year, month, date etc which depends on eachother)

Re: Set Analysis - second selection?

Wed, 22 Aug 2012 12:21:44 GMT

Hi swuehl,

that's it. A bug is also possible, but I get it with your suggestion.

The missing value is existing, but I could imagine, it is a problem of data and not relevant in this case.

Thank you very much for your help, I have learnt a lot.

swuehl schrieb:
I believe the red underline is a bug in the syntax checker (with the empty p() function, you could use the equivalent p(%COUNTRYID) to get rid of the red underline).
But I updated the expression to include the restriction in TRANSYEAR also in the p() function, see above. This should resolve the missing value.

Re: Set Analysis - second selection?

Wed, 22 Aug 2012 12:23:11 GMT

Hi GandalfGrey,

also thank you very much for your help, I will cover your proposal for the next time.

GandalfGray schrieb:
Hi again
There is more than one way to do it but
what I normally prefer to do is stuff like:
Sum( {$<dim1={whatever}, dim2=, dim3=>} amount)
That is I use the current selection, but sets dim1 to another value of my choice, and unsets any choices in dim2 and dim3
(You often need to use this technique when dealing with year, month, date etc which depends on eachother)