Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

SET ANALYSIS - USING MULTIPLE FILTERS ON SINGLE TABLE

I have an Item transaction (ledger) table where I record transaction dates, manufacturing center, main products, components and qty.

How do I use Set Analysis to filter the list of main products and components for an evaluation period whilst ignoring both the period and manufacturing center?

I want to calculate inventory movement to date for the product, irrespective of the manufacturing centre and period.

Year

Month

Product

Open. Bal.

Movement for Period

Closing Bal.

2006

8

XXXXXXXX

A.

B.

C.



A. I want to calculate the opening balance based on ALL movements in the table for product XXXXXXXX where the trx date is less than the first day of 08/2006.

· =SUM({$<[Year]= ,[Month]=,[Main Product]= ,[Component]=, [Manufacturing Center] =,[Posting Date]={"<$(=MAKEDATE([Year],[Month],1))"}>}[Qty])

B. I want to calculate the movement during the period 08/2006.

· =SUM({$<[Main Product]= >}[Qty])

C. I want to calculate the closing balance for product XXXXXXXX by summing all movements in the table up until the last day of 08/2006

· =SUM({$<[Year]= ,[Month]=,[Main Product]= ,[Component]=, [Manufacturing Center] =,[Posting Date]={"<$(=MONTHEND(MAKEDATE([Year],[Month],1)))"}>}[Qty])

D. Product XXXXXXXX must be filtered based on the Manufacturing Center, and Transactions that have been posted during the period 08/2006

· By using the "[Component] = " in the calculating the O/B the result lists ALL components.