Ignoring Filters

[#1]

Our company is using Qlikview to display Inventory data. There are A LOT of fields but here is basically what I want to do.

The basic table looks like this

PART#    WAREHOUSE    QUANTITY    YEARUSAGE

1234          24                        100                        0

1234          52                        500                    100

1234          64                        0                        75

1234          32                        200                    115

But imagine there are thousands of part#s and a hundred different warehouses. The same part #s are stored in multiple warehouses.

I am trying to find "dead inventory" (part#s in warehouse where the 'yearusage' is zero) and the find all the other warehouses that DO have usage on that same part.

I can filter the master data table to show me all the part #s in a specific warehouse that have Zero YearUsage (dead stock)

    Select Warehouse = 24 and YearUsage = 0

Now I want to see all the other warehouses that carry that same part to see who DOES have usage on that part # so i can get the inventory transferred over to the warehouse that is using it.

But because I selected "Warehouse = 24" and "YearUsage = 0" I don't know how to build another table that ignores the warehouse and yearusage filters. I want to keep the Part# filter but I want the table to ignore the other 2 filters so I can see other warehouse usage and decide what to do with the "dead inventory" at warehouse 24.

HELP!

[Oleg_Troyansky]

Hi Campbell,

welcome to the QlikView community!

If you need to ignore certain selections, you can use Set Analysis - this is a special type of a condition that can alter the set of data that goes into the given aggregation. You will need to look up what Set Analysis is and learn the basic syntax. For your specific needs, the expression would look like this:

sum({<Warehouse=, YearUsage= >} YearUsage)

Or, if you wanted to create a list of other warehouses with YearUsage, you could do something like this:

concat( {<Warehouse=, YearUsage={">0"}>}  Warehouse, ', ')

cheers,

Oleg Troyansky

Learn Advanced Set Analysis and Advance Aggregation with me at the Masters Summit for QlikView!

[sasiparupudi1]

use alternate states

[gautik92]

try to use alternate states

[#5]

WWhat are alternate states?‌

[qlikmsg4u]

go through this link

what is alternate states in Qlikview ?

ALTERNATE STATES

[#7]

You guys are awesome! That worked perfectly. I have some great new tools. Thanks.

I have some more questions so get ready.

[MarcoWedel]

maybe you could use the p() function on the PART# number field in a set expression.

regards

Marco

[MarcoWedel]

maybe like this:

=Sum({1<PART#=p(PART#)>} YEARUSAGE)

table1:
LOAD RecNo() as WAREHOUSE,
     Ceil(Rand()*9000)+1000 as PART#,
     Floor(Rand()*500,100) as QUANTITY,
     Floor(Rand()*100,10) as YEARUSAGE
AutoGenerate 100
While IterNo()<=1000;

hope this helps

regards

Marco