units vs dollars

[#1]

Hello, I am trying to show retail dollars in a chart. All of my data is in unit form but i have a field of retail cost. Units are showing up fine by month, but retail dollars are just adding together and show the same number for every month. I can't figure out what I am doing wrong. My expressions are just =sum({<Year={"YYYY"}>}Unit_Sales) or Retail_Cost by year...

My script looks like this:

TEMP_SALES_DATA:

CrossTable(MONTH, UNIT_SALES, 28)
LOAD

     [Store #],
     [Store Name],
     UPC,
     SAP,
     [SKU],
    Status,
     [Item Description],
     [Cost],
     [Retail Cost],  <-- the dollars i need to show by month just like units
     [On-Hand],
     [On-Order],
     Month1,
     Month0,
     [Month-1],
     [Month-2],
     [Month-3],
     [Month-4],
     [Month-5],
     [Month-6],
     [Month-7],
     [Month-8],
     [Month-9],
     [Month-10],
     [Month-11],
     [Month-12],
     [Month-13],
     [Month-14],
     [Month-15],
     [Month-16],
     [Month-17],
     [Month-18],
     [Month-19],
     [Month-20],
     [Month-21],
     [Month-22]

FROM
[\\NETWORK\QLIKVIEW\SALES\TEAM\FOLDER\UPLOAD.xlsm]
(ooxml, embedded labels, table is [FINAL DATA]);


FINAL_SALES_DATA:
LOAD
     [Store #],
     [Store Name],
     UPC,
     SAP,
     [SKU],
     Status,
     [Item Description],
     [Retail Cost],
     [On-Hand],
     [On-Order]
     ApplyMap('DATE_MAP',MONTH,0) as Date,
     SAP & ApplyMap('DATE_MAP',MONTH,0) as SAPDateKEY,
     UNIT_SALES

Resident TEMP_SALES_DATA;
Drop Table TEMP_SALES_DATA;


Thanks for your help!!!!!

[awhitfield]

Hi Parrish,

can you post your qvw? Daft question but are you multiplying the retail cost by the qty of units sold?

Andy

[#3]

Apply Aggregate function like SUM and Group by ..

TEMP_SALES_DATA:

CrossTable(MONTH, UNIT_SALES, 28)
LOAD

     [Store #],
     [Store Name],
     UPC,
     SAP,
     [SKU],
    Status,
     [Item Description],
     [Cost],
     [Retail Cost],  <-- the dollars i need to show by month just like units
     [On-Hand],
     [On-Order],
     Month1,
     Month0,
     [Month-1],
     [Month-2],
     [Month-3],
     [Month-4],
     [Month-5],
     [Month-6],
     [Month-7],
     [Month-8],
     [Month-9],
     [Month-10],
     [Month-11],
     [Month-12],
     [Month-13],
     [Month-14],
     [Month-15],
     [Month-16],
     [Month-17],
     [Month-18],
     [Month-19],
     [Month-20],
     [Month-21],
     [Month-22]

FROM
[\\NETWORK\QLIKVIEW\SALES\TEAM\FOLDER\UPLOAD.xlsm]
(ooxml, embedded labels, table is [FINAL DATA]);


FINAL_SALES_DATA:
LOAD
     [Store #],
     [Store Name],
     UPC,
     SAP,
     [SKU],
     Status,
     [Item Description],
    SUM( [Retail Cost]) as RetailCost,
     [On-Hand],
     [On-Order]
     ApplyMap('DATE_MAP',MONTH,0) as Date,
     SAP & ApplyMap('DATE_MAP',MONTH,0) as SAPDateKEY,
     SUM(UNIT_SALES) as Sales
Group BY [Store #],
     [Store Name],
     UPC,
     SAP,
     [SKU],
     Status,
     [Item Description],
  [On-Hand],
     [On-Order],
MONTH,

SAP

RESIDENT

TEMP_SALES_DATA;

DROP table TEMP_SALES_DATA;

[Peter_Cammaert]

Rather simple. Originally, your data is organized more or less like this (example):

Product  Retail_Cost  Month1  Month2  Month3 ...

ABC      1000         5       7       12     ...

After the crosstable load, you end up with a table like this:

Product  Retail_Cost  Month   Unit_Sales

ABC      1000         Month1  5

ABC      1000         Month2  7

ABC      1000         Month3  12

:

thereby duplicating the same Retail_Cost for all months that have Unit_Sales. Is this what you intended to accomplish? I don't think so, but I'm not sure.

Also, the value 28 in the last CROSSTABLE() prefix parameter skips/duplicates a lot of Month-x columns. If you are not using them, then why LOAD them?

Peter