5 Replies Latest reply: Apr 10, 2017 5:38 AM by Balaji J RSS

    How to cross refer another table with aggr function in Qlik Sense

    Balaji J

      Hi,


      Please note this I need it for Qlik Sense.

       

      I am very new to Qlik Sense. I am stuck with below situation and don't know how to solve this. Any guidance and help is a great help.

      Below are sample tables and the formula that I got so far.

       

       

       

      Actual Cost table

      ProdTimeCostQty
      Prod1T150050
      Prod1

      T2

      1500100
      Prod2T12000100
      Prod2T2125050
      Prod3T26000120

       

      Now what I wanted to do is this:

      I want to take avg unit cost difference at each Product level between T2 and T1 time periods and multiply with T2 period quantities at SKU level and sum it as 'Total cost increase $'. I wrote the below formula for that:

       

      sum(aggr(((sum({<Time = {'T2'}>}Cost)/sum({<Time = {'T2'}>}Qty)) - (sum({<Time = {'T1'}>}Cost)/sum({<Time = {'T1'}>}Qty)))*(Sum({<Time = {'T2'}>}Qty)), Prod))

       

      The output I am getting for this is: 750

       

      This logic works as intended as long as a Product present in both the periods but Prod3 is completely omitted from the calculations, which I do not want. I have another unit cost reference tabel for T1 period for all the Products (including the ones that were not sold). Since in the above table Prod3 missing a transaction in T1 period, I want to use the avg unit cost of Prod3 in the place of

      (sum({<Time = {'T1'}>}Cost)/sum({<Time = {'T1'}>}Qty)). I want to cross refer the unit cost table only when that product misses transaction in T1 period.


      I am stuck with this logic and unable to think any solution that I can use. The sample Avg unit cost table is as below:

       

      ProdTimeUnit Cost
      Prod1T110
      Prod2T120
      Prod3T140


      The final aggregation outcome I am looking to get in this example is 1950 (750 + 1200 where 1200 came from (50-40)*120 from Prod3). I do not want to use the Avg unit cost reference table for ALL the products. Use it only when the transaction is missing in T1 period.


      Please let me know if you need any details that I missed to mention here.


      Thanks in advance for the help.


      Thanks & Regards,

      Balaji