topic How do i calculate the correct data? in QlikView

How do i calculate the correct data?

Anonymous — Wed, 14 Jun 2017 14:34:02 GMT

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

Re: How do i calculate the correct data?

Nicole-Smith — Wed, 14 Jun 2017 15:52:46 GMT

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.

Re: How do i calculate the correct data?

Anonymous — Thu, 15 Jun 2017 08:12:56 GMT

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

This does exactly what i intended to achieve.