topic Re: Pivot chart with unlinked tables in QlikView

Pivot chart with unlinked tables

Wed, 05 Dec 2012 15:37:16 GMT

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 — Wed, 05 Dec 2012 18:19:41 GMT

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

vgutkovsky — Wed, 05 Dec 2012 18:38:58 GMT

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

Re: Pivot chart with unlinked tables

Wed, 05 Dec 2012 18:46:31 GMT

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

Wed, 05 Dec 2012 18:52:07 GMT

Vlad,

You are on the same track as Gysbert and I believe that it isn't what I need for my situation. Perhaps my answer to Gysbert can clarify my problem.

Thanks

Re: Pivot chart with unlinked tables

vgutkovsky — Wed, 05 Dec 2012 18:56:19 GMT

Ah, well in that case I would still use my data model and just add 1 more table: a date island. The fields from this date island will be your chart dimensions and you will need to use a combination of IF statement and set analysis to check that each component (Sales and Cost) are within the period you want.

Vlad

Re: Pivot chart with unlinked tables

Wed, 05 Dec 2012 19:10:38 GMT

When you say " date island", you mean an unlinked table with months and years? This would be used instead of setting variables?

The "use a combination of IF statement and set analysis to check that each component (Sales and Cost) are within the period you want" is what is troubling me!!!

Can you give me an example in that so I can test it in my pivot chart?

Re: Pivot chart with unlinked tables

vgutkovsky — Thu, 06 Dec 2012 14:06:27 GMT

See attached.

Regards,

Vlad

Re: Pivot chart with unlinked tables

Tue, 11 Dec 2012 09:59:52 GMT

Vlad,

Thank you very much for your time and effort. The attached file is absolutely correct. I have to make all the adjustments to fit it in my data, but it is pointing me to the right path.

Thanks once more.