Qlik Community

Qlik Sense App Development

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

mikegrattan
Contributor II

Division of variables returns the value of the numerator

I am trying to divide picked orders load time per pallet by non-picked orders load time per pallet to get a ratio.

First variable vTimeToLoad_Avg_Pick:  Avg({<PickFlag={1}>} MinutesToLoad)

Second variable vPalletsPerOrder_Pick:  Avg(Aggr(Avg({<PickFlag={1}>} DailyPallets), SalesOrder))

Resulting variable calc vMinToLoadPerPallet_Avg_Pick:  $(vTimeToLoad_Avg_Pick) / $(vPalletsPerOrder_Pick)

This results in a value of 9.12 minutes to load per pallet for a picked order.

For nonpick orders the math is the same, but the value of the PickFlag is changed to 0.  The result is a value of 6.36 minutes per pallet to load non-picked orders.

The result of dividing vMinToLoadPerPallet_Avg_Pick / vMinToLoadPerPallet_Avg_NonPick should be the result of dividing 9.12 by 6.36 which would equal 1.43.  However, I'm getting a result of 9.12.  I've tried putting parentheses around each variable, but then I get a result of Null ("-").  I've tried with and without $ and parens like this:


$(vMinToLoadPerPallet_Avg_Pick) / $(vMinToLoadPerPallet_Avg_NonPick)


and I get null result for that as well.


I also tried using the Div function and also got similar results.


Anything else I can try?


Tags (2)
1 Solution

Accepted Solutions
aarkay29
Valued Contributor

Re: Division of variables returns the value of the numerator

you can use  Column(1)/Column(2) for ratio

11 Replies
aarkay29
Valued Contributor

Re: Division of variables returns the value of the numerator

Could you be able to share the app ??

mikegrattan
Contributor II

Re: Division of variables returns the value of the numerator

I have attached a copy of the app.  Please feel free to take a look.  Thank you.

aarkay29
Valued Contributor

Re: Division of variables returns the value of the numerator

Add '=' sign to the variable exp

vMinToLoadPerPallet_Avg_Pick=$(vTimeToLoad_Avg_Pick) / $(vPalletsPerOrder_Pick)

vMinToLoadPerPallet_Avg_NonPick: =$(vTimeToLoad_Avg_NonPick) / $(vPalletsPerOrder_NonPick)

and Use this exp vMinToLoadPerPallet_Avg_Pick / vMinToLoadPerPallet_Avg_NonPick

mikegrattan
Contributor II

Re: Division of variables returns the value of the numerator

I added the = sign to both pick and nonpick formulas and I'm now getting the ratio result, which is a step forward.  Unfortunately, my calcs are now showing the same result for all years and all warehouses.  If you can take a look at the pivot table "Average Time to Load Per Pallet" on the sheet "Load Time factors - Pick vs Non-Pick" perhaps you can see what I mean.

Thank you for your assistance.

aarkay29
Valued Contributor

Re: Division of variables returns the value of the numerator

Seems to be working for me

PFA with new sheet

mikegrattan
Contributor II

Re: Division of variables returns the value of the numerator

Using a pivot table I am seeing the numbers duplicate for all years and all warehouses.

aarkay29
Valued Contributor

Re: Division of variables returns the value of the numerator

Sorry

Remove the '=' sign for variables

and try using this exp

$(=$(vMinToLoadPerPallet_Avg_Pick))

/

$(=$(vMinToLoadPerPallet_Avg_NonPick))

mikegrattan
Contributor II

Re: Division of variables returns the value of the numerator

That fixed the Pick and NonPick numbers in the pivot table, but the ratio calc is coming up 1.55 for every combination of year and warehouse...strangely.

MinToLoadPerPallet.jpg

mikegrattan
Contributor II

Re: Division of variables returns the value of the numerator

I've tried various combinations of using the = sign for formulas/variables and still coming up with strange results.  Any other ideas?

Community Browser