topic Set Analysis - Use what is Filtered Explicitly in QlikView

Set Analysis - Use what is Filtered Explicitly

Mon, 02 Mar 2015 19:12:41 GMT

I'm still working on my set analysis with different Objects (Pencils, Pens, Crayons) - in my real example I have dates that are nearly always filtered to a time frame.

I want to filter to a Pencil and it show me the total for the filtered time range. This sounds so easy but I'm still struggling with Set Analysis in general.

My current approach is give me the total for the possible dates using P(). However this is NOT the same as what timeframe is being filtered to. What P() is giving me are the totals for the dates that apply to that filtered object list.

See below - I would expect the total Q(last column) for Pencils between the period of 6/1 through 12/1. As you can see as I cycle between store B and C it changes from 60 to 70. I know this is because of how possible P() function works.

I feel =GetFieldSelections(Trans_Date) might be the key but as I try to build out a string and use it in set analysis, things like NOT x, y, z seem to get in the way. Before I invest more time - is there any easier way?

sum({1<ObjectName=p(ObjectName), Trans_Date = P(Trans_Date)>} Quantity)

Re: Set Analysis - Use what is Filtered Explicitly

sunny_talwar — Mon, 02 Mar 2015 19:56:03 GMT

Try this expression instead for Total Quantity: =Sum({<StoreName = >}Quantity)

Best,

Re: Set Analysis - Use what is Filtered Explicitly

Mon, 02 Mar 2015 20:16:44 GMT

If I were to go that route, I'm assuming I would need to include all the available fields that could be filtered on as in my real world file there are 50+ ways to slice the data down.

Re: Set Analysis - Use what is Filtered Explicitly

sunny_talwar — Mon, 02 Mar 2015 20:27:19 GMT

Well so here you are just telling the expression not to change with change in StoreName. I guess do you have a lot of field names where you don't want your expression to change?

Best,

Re: Set Analysis - Use what is Filtered Explicitly

Mon, 02 Mar 2015 20:32:11 GMT

Yes there are a lot of fields.

So I guess I want the total for the object to ignore all filters except the time domain. I think I've seen this on community elsewhere that uses $Fields. I'll probably need to go that direction.

Re: Set Analysis - Use what is Filtered Explicitly

Tue, 03 Mar 2015 13:13:33 GMT

I rewrote it using the expression found here: http://community.qlik.com/docs/DOC-1334

I started with what was provided on that link to ignore all except Trans_Date. Then added in the P(ObjectID) and excluded it from the filters that are ignored.

=sum({$<ObjectID=p(ObjectID),[$(='[' & Concat({1<$Field-={'Trans_Date','ObjectID'}>}distinct $Field,']=,[')&']=')>} Quantity)

I think I'm getting the functionality that I wanted.

I select "Pencil" and "Store B" both the ignore storename set analysis expression ( =Sum({<StoreName = >}Quantity) ) and the new expression above give the same answer. See the total quantity on the upper table and lower table are both "130"

Now I click on Rating = 10 (what shows in white above). Here you can see the difference. The new expression still reads 130 - like I need it to. The top one shows 70 as expected but not what is wanted. I like this solution as it is dynamic as I add more fields/filters/etc.

Re: Set Analysis - Use what is Filtered Explicitly

sunny_talwar — Tue, 03 Mar 2015 13:41:55 GMT

Thanks for taking out time to post your solution here. I am sure a lot of people will find this useful.

Best,