8 Replies Latest reply: Dec 11, 2012 4:59 AM by A M RSS

    Pivot chart with unlinked tables

      Hello to all,

      I am relatively new to qlik and (unfortunately) I  have a project that I can’t sort out.

      The case is as follows:

       

      I have loaded two resident tables (SQL server):

       

      • SALES having data from all invoices (DATE,YEAR, MONTH, ITEM, QTY)
      • COSTPRICES that has a cost price for all items for all years and all months (YEAR,MONTH,ITEM,COSTPRICE)

       

      I have created 3 variables:

      • sales start date
      • sales end date
      • Cost period

       

      I need a pivot chart with the expressions below:

      • SALES QTY (from sales start date to sales end date)
      • COST (qty * COSTPRICE) FOR THE SELECTED by the user cost period

       

      (the dimensions will vary ex. items, months etc)

       

      I have not linked the 2 tables since that would give wrong results (either in sold quantity or cost).

       

      There is no problem in the pivot chart to get sum(QTY) for the selected range of dates.

      It is that, by some means, I have to get the ONE cost price for each invoice line (based on item, selected period and sales year)

       

      I believe that I should use some kind of function that fetches the right COST  PRICE for every item and year in the selected
      month.

       

      Since I am on a dead end, any help would be greatly appreciated.

       

      PS: I hope I made the case as clear as possible to help you understand what I am looking for.

        • Re: Pivot chart with unlinked tables
          Gysbert Wassenaar

          If there's a way to determine the right cost price for an item in a year, then you can link the tables using that logic. And that's what you should do. From your description it looks like you could create a key in both tables from ITEM&MONTH&YEAR and link the tables on that key.

            • Re: Pivot chart with unlinked tables

              Hi,

               

              If I link both tables on item&month&year then when I select a certain period, the calculation in the expression of COST (qty * COSTPRICE) will be done with the respective costprice AND NOT the price that I want.

               

              To be more precise:

               

              The normal situation (that you are talking about) is:

              I have sold 10 pieces of a product in May 2012.

              The items sell price is 6$ so I earned 10*6$=60$

              The cost price (in costprice table) for that period is 3$.

              The cost of this sale is 10*3$=30$

               

              My situation is a bit more complex:

              I have sold 10 pieces of a product in May 2012.

              The items sell price is 6$ so I earned 10*6$=60$

               

              The cost price THAT I WANT TO USE is from the December period and is 5$

              The cost of this sale is 10*5$=50$

               

              I hope now it is more clear.Thanks again for your time.

            • Re: Pivot chart with unlinked tables
              Vlad Gutkovsky

              Gysbert is correct that you need some sort of relationship between the 2 tables to display data from both of them in the same chart. I would suggest concatenating rather than linking--i.e. try to create a true star schema. This is beneficial both in terms of performance and overall aesthetics (since both tables are fact tables). Try something like this:

               

              Fact:
              LOAD 
                  DATE as DateKey,
                  ITEM,
                  QTY,
                  1 as SalesRow
              RESIDENT SALES;
              
              CONCANTENATE (Fact) LOAD
                  YEAR & '|' num(MONTH) as DateKey, //this assumes you have a 4-digit year and numeric/dual month
                  ITEM,
                  COSTPRICE
                  1 as CostRow
              RESIDENT COSTPRICES;
              
              DROP TABLES SALES,COSTPRICES;
              
              //Next, create a master calendar that will contain all your sales dates
              min_max_dates:
              LOAD
                  min(DateKey) as mindate,
                  max(DateKey) as maxdate
              RESIDENT Fact
              WHERE SalesRow=1
              ;
              
              LET vMinDate = peek('mindate');
              LET vMaxDate = peek('maxdate');
              LET vNoDays = '$(vMaxDate)' - '$(vMinDate)' + 1;
              
              DROP TABLE min_max_dates;
              
              Calendar:
              LOAD
                  DateKey,
                  DateKey as Date,
                  year(DateKey) as Year,
                  month(DateKey) as Month,
                  date(monthstart(DateKey),'MMM YYYY') as MonthYear,
                  'Q' & ceil(month(DateKey)/3) as Quarter
              ;
              LOAD
                  date('$(vMinDate)' + recno() - 1) as DateKey
              AUTOGENERATE $(vNoDays);
              
              //Adding "generic key" rows to master calendar to create matches on Cost data
              CONCATENATE (Calendar) LOAD
                  Year & '|' & num(Month) as DateKey,
                  Date,
                  Year,
                  Month,
                  MonthYear,
                  Quarter
              RESIDENT Calendar;
              

               

              Regards,

              Vlad