Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

engxiong_lim
New 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

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

Something like this?

Capture.PNG

engxiong_lim
New Contributor

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

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