topic Re: How to set up data model in QlikView

How to set up data model

Fri, 29 Aug 2014 21:13:01 GMT

I'm really struggling here.

In my report, I want to show the future forecasts vs. past sales. I would like to have two different columns, one that shows the future forecasts and the other to show what the actual sales were last year, previous year and so on.

Does anyone know the best way to do this? I've tried multiple attempts at splitting week and year and it does not work the way I would like to see it. Please see the attached image and see if anyone can help me with this....

This is how I would like to see it .... see the attachment for a full excel file of how forecast and sales input looks like. Any help will be truly appreciated. really struggling with this one..

Week	Product	Plant	Sales in 2012	Sales in 2013	Forecast
201432	A	1	1100	1000	900
201432	B	2	2100	2000	1900
201432	C	3	3100	3000	2900
201432	D	4	4100	4000	3900
201433	A	1	5100	5000	4900
201433	B	2	6100	6000	5900
201433	C	3	7100	7000	6900
201433	D	4	8100	8000	7900
201434	A	1	9100	9000	8900

Re: How to set up data model

MarcoWedel — Fri, 29 Aug 2014 22:19:01 GMT

Hi,

one solution might be:

tabForecastSales:
LOAD *,
    Dual(PurgeChar(WeekName, '/'), WeekName) as Week,
    WeekStart(WeekName) as WeekStart,
    Week(WeekName) as WeekNum,
    WeekYear(WeekName) as WeekYear,
    Year(WeekName) as Year,
    Month(WeekName) as Month;
LOAD WeekName(MakeWeekDate(Left(Week,4), Right(Week, Len(Week)-4))) as WeekName, 
    Product,
    Type,
    Plant,
    Money(Forecast) as Amount,
    'Forecast' as AmountType
FROM [http://community.qlik.com/servlet/JiveServlet/download/598233-122509/sales%20vs.%20forecast.xls]
(biff, embedded labels, table is [Forecast File$]);

LOAD *,
    Dual(PurgeChar(WeekName, '/'), WeekName) as Week,
    WeekStart(WeekName) as WeekStart,
    Week(WeekName) as WeekNum,
    WeekYear(WeekName) as WeekYear,
    Year(WeekName) as Year,
    Month(WeekName) as Month;
LOAD WeekName(MakeWeekDate(Left(Week,4), Right(Week, Len(Week)-4))) as WeekName, 
    Product,
    Type,
    Plant,
    Money(Sales) as Amount,
    'Sales' as AmountType
FROM [http://community.qlik.com/servlet/JiveServlet/download/598233-122509/sales%20vs.%20forecast.xls]
(biff, embedded labels, table is [Sales File$]);

hope this helps

regards

Marco

Re: How to set up data model

Fri, 29 Aug 2014 22:20:11 GMT

Is this possible in a straight chart?

Re: Re: How to set up data model

MarcoWedel — Fri, 29 Aug 2014 22:38:00 GMT

hope this helps

regards

Marco

Re: Re: Re: How to set up data model

Tue, 02 Sep 2014 15:18:26 GMT

do you know why this is happening?

Re: Re: Re: Re: How to set up data model

MarcoWedel — Tue, 02 Sep 2014 19:51:53 GMT

Hi,

the expression didn't work, if there are no forecast values for a specific week.

This one should be better:

hope this helps

regards

Marco