Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

Anonymous
Not applicable
Author

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

This does exactly what i intended to achieve.