Comparison between actual sales and forecast using a script

[#1]

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.

[Anonymous · Accepted solution]

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

[#3]

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.

[Anonymous]

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