topic Help with a product mix forecast in QlikView

Help with a product mix forecast

hobanwashburne — Tue, 04 Aug 2015 18:28:29 GMT

I have attached a sample data set that is representative of a MUCH larger data set.

We received forecasts at a customer level for the upcoming year, I would then like to apply those forecasted changes to the actual sales in FY2014 resulting in an FY2015 Projection (this has been done manually in the table CompleteDataSet). Also where we did not receive a forecast (two of the customers) we would like to replace the null with a standard value (13% was manually entered in the table CompleteDataSet).

The output goal would be a product mix for each branch/channel.

The final result for Branch 100 Channel Dist should be:

A: 31%

B: 16%

C: 11%

😧 27%

E: 14%

Re: Help with a product mix forecast

swuehl — Tue, 04 Aug 2015 18:41:37 GMT

You can do it like this:

SampleDataSet:

LOAD *,

ForecastedChange * FY2014+FY2014 as FY2015_Projection;

LOAD Branch, Channel, ProductFamily, FY2014,

if(len(trim(ForecastedChange)),ForecastedChange,0.13) as ForecastedChange;

LOAD * INLINE [

Branch, Channel, Customer, ProductFamily, FY2014, ForecastedChange

100, Dist, 7871, A, 927, 0.11

....

];

Re: Help with a product mix forecast

Kushal_Chawda — Tue, 04 Aug 2015 18:49:41 GMT

Please see the attached.

Re: Help with a product mix forecast

hobanwashburne — Tue, 04 Aug 2015 19:11:36 GMT

Thank you both (you both had essentially the same suggestion). However, I'm still in need of the second part. I need to be able to generate a chart that looks similar to the image below.

Re: Help with a product mix forecast

swuehl — Tue, 04 Aug 2015 19:45:12 GMT

Something like attached?

Re: Help with a product mix forecast

hobanwashburne — Tue, 04 Aug 2015 19:50:00 GMT

Exactly.

It was the " sum(total<Branch,Channel> FY2015_Projection) that I couldn't figure out.

Thank you!