# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

## stumped and need help with calculation

I need to determine the best number of each record (of a range) by using a calculation to measure against a qty. Each record could contain a different range. My table data looks like this:

Product, Qty, Min, Max
a, 140, 25, 30
b, 130, 20, 25
c, 150, 30, 40
]
;

So for product 'a', my result needs to be 28 (largest calculated value) based on the calculation below:

 Qty Num Calculated result = num*floor(Qty/Num) 140 25 125 140 26 130 140 27 135 140 28 140 140 29 116 140 30 120

Hopefully, I am overthinking it.  Any advice you could provide would be greatly appreciated, thank you.

1 Solution

Accepted Solutions
MVP

## Re: stumped and need help with calculation

sum(floor(Qty / Range))

A:

Product, Qty, Min, Max

a, 140, 25, 30

b, 130, 20, 25

c, 150, 30, 40

];

AA:

Product,

Min + IterNo() -1 as Range,

Min,

Max

Resident A

While Min + iterno() -1 <= Max;

8 Replies
MVP

## Re: stumped and need help with calculation

You are looking for this in script or front end?

Not applicable

## Re: stumped and need help with calculation

in the script, if possible.

Not applicable

MVP

## Re: stumped and need help with calculation

sum(floor(Qty / Range))

A:

Product, Qty, Min, Max

a, 140, 25, 30

b, 130, 20, 25

c, 150, 30, 40

];

AA:

Product,

Min + IterNo() -1 as Range,

Min,

Max

Resident A

While Min + iterno() -1 <= Max;

Honored Contributor III

## Re: stumped and need help with calculation

May be like this, you may join the new table or can keep separate as per the need -

SUB CalNum (vNumber,SubQty,SubMin,SubMax)

vNum = 0;

For j= SubMin to SubMax

Let vStepNum = \$(j)*floor(SubQty/\$(j));

If vStepNum>vNum then

Let vNum = vStepNum;

Let vNumber = \$(j);

ENDIF

Next

ENDSUB

Source:

Product, Qty, Min, Max

a, 140, 25, 30

b, 130, 20, 25

c, 150, 30, 40

];

Let vNoOfRows = NoOfRows('Source')-1;

For i = 0 to vNoOfRows

Let vNumber = 0;

Let vQty = Peek('Qty',\$(i),'Source');

Let vMin = Peek('Min',\$(i),'Source');

Let vMax = Peek('Max',\$(i),'Source');

Let vProduct = Peek('Product',\$(i),'Source');

Call CalNum(vNumber,vQty,vMin,vMax);

Temp:

\$(vNumber) as Num

AutoGenerate(1);

Next

Not applicable

## Re: stumped and need help with calculation

Many, Many Thanks. This worked perfectly. I am officially a HUGE fan of this community.

Honored Contributor III

## Re: stumped and need help with calculation

Hi Jeff,

Looks like I misunderstood the question. Was curious to know how 32 is the correct value for product B as I see Min-Max range as 20-25.

Thanks,

Not applicable

## Re: stumped and need help with calculation

Yes, you are correct and I did not notice the incorrect formula that was used for the result above. However I was able to use the load script with a different result formula. Note the following script:

Test1:
Product, Qty, Min, Max
a, 140, 25, 30
b, 130, 20, 25
c, 150, 30, 40
]
;

Test2:
Product,
FirstSortedValue(DISTINCT bundleQty,-stackH) as bestBundleQty
Group By Product
;