Hi
I'm very new to Qlikview and need help to create data in following format.
| Customer | Product | YTD budget
| Nov14 forecast
| Dec14 forecast
| Jan14 Forecast
| TOTAL Budget + Forecast | Header 8 |
|---|
| | | | | | | |
Here is part of my script.
//CURRENT FORECAST
/************************/
datarec:
load
costvalue as curr_costvalue,
customerid,
discount as curr_discount,
month_no,
ApplyMap ('CalMMMYYYY',month_no, 'UnKnown') as MMMYYY,
ApplyMap ('CalQTR',month_no, 'UnKnown') as QTR,
productid,
quantity as curr_quantity,
retrodiscount as curr_retrodiscount,
salevalue as curr_salevalue,
units as curr_units,
value as curr_value,
salevalue - (salevalue *((discount + retrodiscount ) / 100. ) ) as curr_nettvalue,
salevalue - (salevalue * ((discount + retrodiscount ) / 100.0000000000000000 ) ) - costvalue as curr_nettprofit;
SELECT *
FROM bkt.forecaststream
WHERE streamid = 141
AND month_no >= 24178;
// BUDGET ( YTD Budget from the same table as above)
/*****************************/
Concatenate(datarec)
load
costvalue as budget_costvalue,
customerid,
discount as budget_discount,
month_no,
ApplyMap ('CalMMMYYYY',month_no, 'UnKnown') as MMMYYY,
ApplyMap ('CalQTR',month_no, 'UnKnown') as QTR,
productid,
quantity as budget_quantity,
retrodiscount as budget_retrodiscount,
salevalue as budget_salevalue,
units as budget_units,
value as budget_value,
salevalue - (salevalue * ((discount + retrodiscount ) / 100. ) ) AS budget_nettvalue,
salevalue - (salevalue * ((discount + retrodiscount ) / 100.0000000000000000 ) ) - costvalue as budget_nettprofit;
SELECT *
FROM bkt.forecaststream
WHERE streamid = 117 // UK Budget 2014-15
AND month_no >= '$(v_budget_from_month)';
AND month_no <= '$(v_budget_to_month)';
