Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - Filter the Dimension and Ignore all Filters for SUM

So I have a listing of objects that come from multiple stores.  For example I might be able to get a Pencil, a Crayon or a Pen from 10 different stores at all different prices.   I have a fact table that lists orders for the last few years.

I would want to be able to filter to just Pencils and one store and show a line in a straight table that says

Object     Quantity Ordered     Cost    

Pencil       100                         $10         

The above can be done without any Set Analysis and is the default behavior of a straight table.

Now I want to add a column that says the above (filtered to Pencil and 1 store)  but also shows the sum of all stores for quantity and cost.

Object     Quantity Ordered     Cost     Total Q     Total Cost

Pencil       100                         $10       500          $550  

When I use {1} to ignore all filters, I get something like this when filtered to 1 store and Pencil.   The Crayon and Pen show up even though they have been filtered away.   I understand this behavior but how do I prevent it from happening.

Object     Quantity Ordered     Cost     Total Q     Total Cost

Pencil       100                         $10       500          $550  

Crayon       0                              0        50            $20  

Pen            0                              0        1500        $800  

I've been reading through community and Tom Mackay's presentation on Set Analysis and haven't been able to come up with an answer.   I've been playing with using P() but so far that hasn't given the results I want.

1 Solution

Accepted Solutions

Try maybe like this:

Sum({1<Object = p(Object)>} [Quantity Ordered])

Sum({1<Object = p(Object)>} [Cost])

HTH

Best,

S

View solution in original post

5 Replies
MVP
MVP

Can you provide few lines of Sample data?

Specialist II
Specialist II

use {<store=>}

as your set expression for "Total Q and "Total Cost"

This way the set expression will disregard any filter on the store field

Try maybe like this:

Sum({1<Object = p(Object)>} [Quantity Ordered])

Sum({1<Object = p(Object)>} [Cost])

HTH

Best,

S

View solution in original post

Not applicable

Answer_Options.JPG

Not applicable

This one seems to work the best as I start to sprawl out of every option that could be filtered on... this makes the most sense.

I was trying to do the same thing but I failed to put the "1" in the correct place.  Attached is the sample with different options being tested.