Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparison between actual sales and forecast using a script

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

3 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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