topic Re: units vs dollars in QlikView

units vs dollars

Tue, 05 May 2015 14:20:54 GMT

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!!!!!

Re: units vs dollars

awhitfield — Tue, 05 May 2015 14:25:17 GMT

Hi Parrish,

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

Andy

Re: units vs dollars

Tue, 05 May 2015 14:26:42 GMT

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;

Re: units vs dollars

Peter_Cammaert — Tue, 05 May 2015 14:35:03 GMT

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