topic Comparison between actual sales and forecast using a script in QlikView

Comparison between actual sales and forecast using a script

Sun, 24 Nov 2013 12:48:11 GMT

How can i compar between "forecast amount" in the forecast table

to "amount" in sales amount table?

the common fields are: mont in forecast table. invoicedate
in sales amount table.

Re: Comparison between actual sales and forecast using a script

Anonymous — Sun, 24 Nov 2013 13:45:25 GMT

Alon

First you need to join your two tables in the load script, by adding the lines below in bold brown.

Give that a go & if needed post the revised qvw and ask for suggestions.

Sales_Actual:

LOAD InvoiceID,

InvoiceDate,

Monthname( InvoiceDate ) as [YearMonth] ,

CustomerID,

ProductID,

Quantity,

Amount,

RequiredDate,

ShippedDate,

TakingDays,

ShipVia,

Cancel_Flag

FROM

data.xlsx

(ooxml, embedded labels, table is [Sales Actual]);

Forecast:

LOAD

Year,

Month,

monthname ( date ( date# ( text (num ( Month , '00' )) & Year , 'MMYYYY') ) ) as [YearMonth] ,

CategoryID,

CategoryName,

Forecast_Amount

FROM

data.xlsx

(ooxml, embedded labels, table is Forecast);

Best Regards, Bill

Re: Comparison between actual sales and forecast using a script

Sun, 24 Nov 2013 16:43:27 GMT

Thank you for the ansuer. works good.

How can i make group by on the sales?

I want to compare total sales by month-

to compare the total sales of january to the total forecast of january etc.

Re: Comparison between actual sales and forecast using a script

Anonymous — Sun, 24 Nov 2013 17:01:59 GMT

Alon

Glad to hear you are on the road.

For your question above, why not you start a new discussion and attach your revised qvw to it.

Best Regards, Bill