topic Re: Help with Sales vs Forecast in QlikView

Help with Sales vs Forecast

Tue, 12 May 2015 18:22:34 GMT

Hello guys

I'm trying to figure out a way to compare my monthly sales with the forecasted value for each month.

I'm getting this circle problem attached (I've cutted other columns to simplify)

Appreciate your help.

Re: Help with Sales vs Forecast

Anonymous — Tue, 12 May 2015 18:43:29 GMT

Question number one - do you need Product level granularity in Sales? If not, there is an easy solution - replace Product with Product Group in Sales, and concatenate Sales and Forecast into one facts table.

If you need Product level in Sales, it will be a more complex solution, probably using a link table in the data model.

Re: Help with Sales vs Forecast

Tue, 12 May 2015 19:53:12 GMT

Dear Michael, thank you for your kindly answer. Could you guide me thru that?

I don't see how can I concatenate my Group Sales into a New Table.

Re: Help with Sales vs Forecast

Anonymous — Tue, 12 May 2015 20:12:03 GMT

Assuming you have resident tables as on our screenshot, here is how you can do it:

ProductGroupMap:
MAPPING LOAD DISTINCT
Product as A,
"Group of Product" as B
RESIDENT ProductTable;

CONCATENATE (Forecast) LOAD
applymap('ProductGroupMap',Product) as "Group of Product", // this replaces Product with the Group of Products
Month,
Sales,
Date
RESIDENT Sales;

As a result, you get one table named Forecast with the both Forecast and Sales data.
Reminder - in this case you don't have Sales on the Product level anymore.

Re: Help with Sales vs Forecast

Thu, 14 May 2015 13:20:12 GMT

Hi Michael,

So that's what I've got:

LOAD Product,

Sales,

Date,

Month(Date) as Month

FROM

(ooxml, embedded labels, table is Sales);

LOAD Product,

[Group of Product]

FROM

(ooxml, embedded labels, table is ProductTable);

LOAD [Group of Product],

Month,

[Monthly Forecast by Group]

FROM

(ooxml, embedded labels, table is Forecast);

ProductGroupMap:

MAPPING LOAD DISTINCT

Product as A,

[Group of Product] as B

RESIDENT ProductTable;

CONCATENATE (Forecast) LOAD

applymap('ProductGroupMap',Product) as [Group of Product],

Month,

Sales,

Date

RESIDENT Sales;

And I'm still getting this updated circle problem:

Appreciate your help,

Re: Help with Sales vs Forecast

Anonymous — Thu, 14 May 2015 13:36:01 GMT

You have to drop the Sales table at the end:

DROP TABLE Sales;

Re: Help with Sales vs Forecast

Thu, 14 May 2015 13:51:11 GMT

Yeah. You got it.

Thanks mate.