Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Too heavy report


Starting point: I have estimated consumption of the components for the next 12 months and I know current component balances on hand. Component suppliers tend have have a order size for the each component (but sometimes order size is 0). I need to report estimated order sizes for the next 12 months.

The problem: I have thousands of different components. I have solved the task by creating virtual on hand balances for the each month. It takes more than a minute to open the report (as well editing, sorting etc) as order sizes and balances for each month are calculated dynamically. How I can speed up report generation? I tried to make calculation with data load editor, but I faced "Nested aggregations not allowed" error.

My calculation goes like this:


Virtual on hand balance for the first month (v2):

If ((OnHand - estimated consumption for the month) > OrderPoint,
(OnHand - estimated consumption for the month),
ceil((OrderPoint - (OnHand - sum(estimated consumption for the month)/OrderSize,1)*OrderSize+OnHand - estimated consumption for the month))

Estimated order amount for the first month (OA1):

if(OnHand - estimated consumption for the month) > OrderPoint,
(OnHand - estimated consumption for the month),
Ceil((OrderPoint - (OnHand - estimated consumption for the month)/OrderSize,1)*OrderSize))


Virtual on hand balance for the month+1 (v3):

If (($(v2) - estimated consumption for the month+1) > OrderPoint,
($(v2)- estimated consumption for the month+1),
ceil((OrderPoint - (OnHand - (estimated consumption for the month+1)/OrderSize,1)*OrderSize+$(v2) - estimated consumption for the month))

Estimated order amount for the month+1 (OA2):

if($(v2)- estimated consumption for the month) > OrderPoint,
($(v2) - estimated consumption for the month),
Ceil((OrderPoint - ($(v2)- estimated consumption for the month)/OrderSize,1)*OrderSize))

v4, v5, v6....OA3, OA4, OA5, OA6 etc.

 

estimate.jpg

0 Replies