Set Analysis to sum included and excluded

[jerry_ile]

This should be simple and I've searched but not found or maybe not understood the answer

I have a pivot table showing Sku's (dimension) Host (Column) and Sales & Store Stock (Measures)

Lets say I have 100 units on hand in 'Host A' across 5 stores (20 units each)

and 2 stores have sales this week but the other 3 had none

The pivot will show correct Sales for the week but the stock figure will now only show 40 stock for the 2 stores that sold that week.

I want the pivot to show the total onhand stock of 100 for that HostA but because my store stock table is linked to the sales table the non sellers get filtered out.

How do I add the excluded stock to the result?

[sunny_talwar]

Difficult to know how you have your data just by reading your explanation. Would it be possible to share a sample to see the issue?

[jerry_ile · Accepted solution]

Hi Sunny,

I think I need a link table in my Data model between the sales and stock tables.

Having trouble creating the link table however as very new to the scripting so I'm trying random combinations (don't judge me)

I have an ODBC link to tables in an Access DB

I've loaded my sales (Sales:) and stock (StoreStock:) tables and added keys on

Host, Store, ChannelType, Sku AS key

I now want to create my linkTable between these but can't get the syntax

I'm assuming I have to pull the distinct values from the ODBC connection or is there a way to use the already loaded Sales: and StoreStock: tables?

Here' the link table load code at the moment

LIB CONNECT TO 'MS ACCESS';
LinkTable:
Load Distinct
    Host & '|' & Store & '|' & ChannelType & '|' & Sku as Key,
    Host,
    Store,
    ChannelType,
    Sku;
SQL SELECT Key   
    FROM "X:\merchandising\retail tools\access database\Qlik Data.accdb".QlikSkuSalesByChannel;

*I get ERROR Field 'Host' not found on this 1st one
LinkTable:
Load Distinct
    StkHost & '|' & StkSTore & '|' & StkChannelType & '|' & StkSku as Key,
    StkHost,
    StkSTore,
    StkChannelType,
    StkSku;
SQL SELECT StkHost & '|' & StkSTore & '|' & StkChannelType & '|' & StkSku as Key
     FROM "X:\merchandising\retail tools\access database\Qlik Data.accdb".QlikStock;