2 Replies Latest reply: Jun 17, 2018 10:31 PM by Eng Xiong Lim RSS

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

    Eng Xiong Lim

      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
      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.:)



      Eng Xiong