Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
engxiong_lim
Contributor
Contributor

How to create filter/dimension to display records based on conditions on aggregated summary tables

Dear all,

I am trying to see what is the best way i can replicate the analysis i have done on excel onto Qlik sense desktop. Attached excel will show the steps i have taken to arrive at the table view i would like to achieve after applying a set of conditions on the pivot table. (QVF attached with what i had attempted as well)

Snapshot of the raw data used:

PO NumberPO ItemVendorBuyerAmountMonthYear
PO11Vendor 1Buyer A50Jun-18
PO12Vendor 1Buyer A30Jun-18
PO13Vendor 1Buyer A20Jun-18
PO14Vendor 1Buyer A10Jun-18
PO15Vendor 1Buyer A10Jun-18
PO16Vendor 1Buyer A30Jun-18
PO21Vendor 1Buyer B200Jun-18
PO22Vendor 1Buyer B200Jun-18
PO23Vendor 1Buyer B100Jun-18
PO31Vendor 2Buyer A50Jun-18
PO32Vendor 2Buyer A50Jun-18
PO33Vendor 2Buyer A50Jun-18
PO34Vendor 2Buyer A50Jun-18

What i would like to achieve is first aggregate the data to find the sum amount per Vendor per Buyer as well as avg PO amount per vendor per vendor

Then, applying a condition of sum amount per vendor per buyer >=150 and avg amount per vendor per buyer <= 150 to display the list of vendor buyer records fulfilling both conditions.

I managed to do aggregation of Sum and Avg as measures in pivot table on QS. While I tried to create a filter/dimension(flag) on master dimension, it does not seem to be working (differ from the results i have on excel model) Hence, i appreciate your help on how to set up a flag to be able to identify them when i need to

Desired table out put when selecting an option to apply conditions.

MonthYear1/6/2018To display table with Sum Amt >= 150 and Avg Amt <=150
150150
VendorBuyerAvg AmtSum AmtAvg PO amt >= 150 (T/F)Sum PO amt >= 150 (T/F)
Vendor 1Buyer A125250FT
Vendor 2Buyer E41165FT
Vendor 2Buyer F135270FT
Vendor 3Buyer G140280FT

Many thanks in advance and looking forward to learn from all of you.:)

Best,

Eng Xiong

2 Replies
sunny_talwar

Something like this?

Capture.PNG

engxiong_lim
Contributor
Contributor
Author

Hi Sunny,

Thanks for the tips. Yes, this is helpful to me in creating the static tables:) However, do you see a possibility in creating a flag / master dimension which i can easily use it in other tables and charts? I was thinking with that i should be able to compare the data sets between those with and without the conditions - which i had attempted to create the flag as master dimensions but without success.

Meanwhile, i will explore using variables in the expressions you provided to create the flexibility for users to conduct scenario tests.

Million thanks again!

Best,

Eng Xiong