Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to set up data model

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..

WeekProductPlantSales in 2012Sales in 2013Forecast
201432A111001000900
201432B2210020001900
201432C3310030002900
201432D4410040003900
201433A1510050004900
201433B2610060005900
201433C3710070006900
201433D4810080007900
201434A1910090008900
Tags (2)
5 Replies
Highlighted

Re: How to set up data model

Hi,

one solution might be:

QlikCommunity_Thread_131306_Pic1.JPG.jpg

QlikCommunity_Thread_131306_Pic2.JPG.jpg

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

Highlighted
Not applicable

Re: How to set up data model

Is this possible in a straight chart?

Highlighted

Re: Re: How to set up data model

QlikCommunity_Thread_131306_Pic3.JPG.jpg

hope this helps

regards

Marco

Highlighted
Not applicable

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

do you know why this is happening?

Highlighted

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

Hi,

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

This one should be better:

QlikCommunity_Thread_131306_Pic4.JPG.jpg

hope this helps

regards

Marco