topic Re: Set Analysis indirect selections in QlikView

Set Analysis indirect selections

derekjones — Wed, 11 Jan 2012 11:45:31 GMT

Hi All

I'm hoping someone can help. I've got an issue using set analysis. What I'm after is to create an expression that sums all of a particular field (ignoring all selections apart from one on the worksheet). I've used the set identifier i.e. '...{1<...' to request a full set of records and then added the indirect set analysis of '...=p()' to include the field I want to use selections for. My issue is that the field in question although not selected in the sheet, if another field restricts the value, it still reduces the data set as if it were selected (an indirect selection of a sort)

I've attached an example to hopefully explain it better.

In my example, I have a dataset which contains:

Category = Product Classification

Vendor = Supplier/Brand

Revenue = Sales Revenue

Area = Sales Area

Now the pie graph I want to build is one where it includes all Vendors (even if one is selected), but is dependent on Category selected and is split by area's of 'My Sales' and ' All Sales' less 'My Sales'. So I have two expressions:

My Sales =sum({1<Area={'My Sales'}, Category=p()>} Revenue)

Other Sales =sum({1<Area={'All Sales'}, Category=p()>} Revenue)-[My Sales]

However if you select a Vendor (use Vendor C as one which highlights the issue well) then because Vendor C has only certain categories in the dataset there is an 'indirect' selection of those categories.

Is there anyway of completely ignoring Vendor selection (either direct through the listbox or indirect) whilst still allowing the user to select category to refine selections?

My thanks in advance for any assistance.

Derek

Set Analysis indirect selections

kaushiknsolanki — Wed, 11 Jan 2012 12:15:36 GMT

Hi,

If you want to ignore any selection in set analysis you will be doing that like shown below example.

Sum({1<Area={'My Sales'}, Category=p(),Vendors = >} Revenue)

Here as you can see the vendors has assigned nothing, it means all the selections of vendors will be ignored.

Hope this will help you.

Regards,

Kaushik Solanki

Set Analysis indirect selections

swuehl — Wed, 11 Jan 2012 12:17:28 GMT

Instead of using the set identifer 1 (to request a full set of records) and then trying to set an appropriate selection on the fields needed, you could also specify a set identifier $ (current selection) and clear the selection on fields you want to disregard (or set the selection to other values):

Not sure if I got your requirements correctly, but try:

=sum({$<Area={'My Sales'}, Vendor= >} Revenue)

resp.

=sum({$<Area={'All Sales'},Vendor= >} Revenue)-[My Sales]

'Vendor= ' is clearing the selections in field Vendor.

Hope this helps,

Stefan

Set Analysis indirect selections

SunilChauhan — Wed, 11 Jan 2012 12:23:12 GMT

try this

My Sales =sum({$<Area={'My Sales'}, Vendor= >} Revenue)

Other Sales =sum({1<Area={'All Sales'},Vendor= >} Revenue)-sum({1<Area={'My Sales'},Vendor = >} Revenue)

hope this helps

Re: Set Analysis indirect selections

derekjones — Wed, 11 Jan 2012 13:06:22 GMT

Hi Kaushik

Sorry, that has not worked, I've amended my example to your format and as you can see, if you select Vendor C, the pie chart changes still

Re: Set Analysis indirect selections

derekjones — Wed, 11 Jan 2012 13:16:02 GMT

Hi Stefan

Yes that works for this simle example, what I omitted to mention is that in my production application there are many other fields/tables, so I have to use a set identifier to exclude all field selections otherwise I would have to mention in the set analysis each individual field I want to exclude.

Not sure if this is possible?

Thanks

Derek

Re: Set Analysis indirect selections

derekjones — Wed, 11 Jan 2012 13:24:07 GMT

Hi Sunil

As per Stefan's answer, I didn't explain, that the example I've attached only contains the specific data to this issue. The actual dataset has many other fields, I need to use a set identifier of 1 to exclude all selections as trying to exclude all individually would not be possible (well possible, but that would be one big Set Analysis!).

Attached is your solution, but with an additional field of 'Other Type' added as an example of other fields which would be in the dataset, imagine over 100 other fields, how can you exclude all but Category selection?

Thanks

Derek

Re: Set Analysis indirect selections

swuehl — Wed, 11 Jan 2012 13:27:39 GMT

Try this:

=sum({1<Area={'My Sales'}, Category=p({<Vendor=>}Category) >} Revenue)

=sum({1<Area={'All Sales'},Category=p({<Vendor=>}Category) >} Revenue)-[My Sales]

Re: Set Analysis indirect selections

derekjones — Wed, 11 Jan 2012 13:40:48 GMT

Hi Stefan

Thanks for the second attempt, works agin on the simple example I originally posted, but using this example bringing in a further field (OtherType), you can see by the example attached, when Vendor C and Type B is selected, the pie chart does still change. I'm trying to achieve the pie chart to ignore all selections (imagine 100 other fields in my real app!) apart from Category.

thanks again

Derek

Re: Set Analysis indirect selections

swuehl — Wed, 11 Jan 2012 13:49:05 GMT

Try

=SUM({$<Area= {'My Sales'},[$(=Concat({1<$Field-={'Category', 'Area'}>}distinct $Field,']= ,[')&']=')>} Revenue)

resp.

=SUM({$<Area= {'All Sales'},[$(=Concat({1<$Field-={'Category', 'Area'}>}distinct $Field,']= ,[')&']=')>} Revenue) - [My Sales]

edit:

attached modified sample

Set Analysis indirect selections

derekjones — Wed, 11 Jan 2012 14:06:42 GMT

Thanks Stefan

Very clever, as far as I can see you are creating individual set analysis exclusions per each field I want to exclude by using a concat.

That works perfectly in the example and looks like it will work in my real world example. Do you think it will have performance issues on a large dataset?

Thanks again.

Derek

Set Analysis indirect selections

swuehl — Wed, 11 Jan 2012 14:33:37 GMT

Derek,

indeed a clever way, but not my own original creation.

The expression part containing the dollar sign expansion

[$(=Concat({1<$Field-={'Category', 'Area'}>}distinct $Field,']= ,[')&']=')

should expand to

[OtherType]= ,[Report]= ,[Revenue]= ,[Vendor]=

i.e. clear all fields except the one listed in $Field-={'Category', 'Area'}

(Note the -= minus equal operator here). $(Field) is a system field containing all your table field names.

I don't think that this will affect your performance, I mean not more than doing the selection in another way.

Regards,

Stefan

Re: Set Analysis indirect selections

juan_c_martinez — Thu, 20 Apr 2023 23:28:24 GMT

Very clever!