Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
wendel_vanrheen
Contributor
Contributor

How do i calculate the correct data?

Hi guy's,

I'm looking for a way to calculate data and i'm a bit stuck in the process. I'm hoping you guys could help me out. Sales reps are entering forecast data of items they think they are going to sale (note a product group is a property of an item). So there for the situation is as follows: The data consists out of reference dates, were the last entry is the actual in time.


With this in mind i'm looking to create a chart with dates (for example year-week, year-month or year-quarter) as dimension and the summed forecast quantity per site, per sales rep, per product group as calculated field.

Example data:

Items:

Load * inline

[itemNumber, productGroup

1, wheel

2, spoke,

3, handlebar

4, pedal

];

ForcastData:

Load * inline

[itemNumber, site, salesRep, forecastDate, quantity

1, london, jack, 01-01-2017, 5

1, london, john, 01-05-2017, 15

1, london, sandra, 01-09-2017, 2

1, london, jack, 01-09-2017, 12

1, amsterdam, bill, 01-09-2017, 2

1, amsterdam, bob, 01-10-2017, 15

2, berlin, mike, 02-03-2017,50

];

So the forecast for wheels in January for the site londen should be 29 (jack- 12, john, 15, sandra 2)....

i was looking into aggr and a set analysis or something a like. Do you guy's have any suggestions??

1 Solution

Accepted Solutions
Nicole-Smith

If you add a month field in your script:

Items:

Load * inline

[itemNumber, productGroup

1, wheel

2, spoke,

3, handlebar

4, pedal

];

ForcastData:

Load *, MonthName(forecastDate) as forecastMonth inline

[itemNumber, site, salesRep, forecastDate, quantity

1, london, jack, 01-01-2017, 5

1, london, john, 01-05-2017, 15

1, london, sandra, 01-09-2017, 2

1, london, jack, 01-09-2017, 12

1, amsterdam, bill, 01-09-2017, 2

1, amsterdam, bob, 01-10-2017, 15

2, berlin, mike, 02-03-2017,50

];

You can use the following on a chart to get what you're looking for:

Dimensions:

forecastMonth

site

Expression:

sum(aggr(FirstSortedValue(quantity, -forecastDate), forecastMonth, site, salesRep))

I've attached an example file with this, and January for London = 29.

View solution in original post

2 Replies
Nicole-Smith

If you add a month field in your script:

Items:

Load * inline

[itemNumber, productGroup

1, wheel

2, spoke,

3, handlebar

4, pedal

];

ForcastData:

Load *, MonthName(forecastDate) as forecastMonth inline

[itemNumber, site, salesRep, forecastDate, quantity

1, london, jack, 01-01-2017, 5

1, london, john, 01-05-2017, 15

1, london, sandra, 01-09-2017, 2

1, london, jack, 01-09-2017, 12

1, amsterdam, bill, 01-09-2017, 2

1, amsterdam, bob, 01-10-2017, 15

2, berlin, mike, 02-03-2017,50

];

You can use the following on a chart to get what you're looking for:

Dimensions:

forecastMonth

site

Expression:

sum(aggr(FirstSortedValue(quantity, -forecastDate), forecastMonth, site, salesRep))

I've attached an example file with this, and January for London = 29.

View solution in original post

wendel_vanrheen
Contributor
Contributor
Author

thank you so much, i think you've been made from the stuff of legends 😉

This does exactly what i intended to achieve.