2 Replies Latest reply: Nov 8, 2017 3:02 PM by John Jackson RSS

    How to associate 2 tables with diff dates

    John Jackson

      I have tried to ask the question before here https://community.qlik.com/thread/280447   but it was probably due to my poor sample data, failed to describe clearly. This time around I have tried my best. as seen below I have 2 tables that have 3 common fields: Country, Region & Payment Group. In the app attached, I have tried to fix that problem with a link table. My main req is to associate [Date Purchased] with[ Date Ordered] so that [Date ordered] is 1 month behind. For eg. data in Mar 2017 for [Date Purchased] should link with data in Feb 2017 for [Date Ordered]. if common fields were not a problem then this was solved by marcowedel by connecting with monthname([Date Ordered],1) -----monthname([Date Purchased]) Thank you Marco for your help.

       

       

      PurchaseSales
      CountryCountry
      RegionRegion
      Payment GroupPayment Group
      Date PurchasedDate Ordered
      SalesCOGS
      Row CountExpenses

       

      to sum it up my req is to sum(COGS) Dec 2016[Date Ordered] for Jan 2017[Date Purchased], Jan 2017[Date Ordered] for Feb 2017[Date Purchased] and so on as suggested below. Purchased month is just a monthstart aggregate. how do I achieve it. Any help would be great.

       

      straight table.PNG