Issue using dynamic update to toggle values for "what if" analysis

[#1]

Hi everyone,

I’m having an issue with dynamic update when deployed on a server. The app involves multiple users changing values with dynamic update. I’m using the values as toggle switches for some calculations, not the intended use of dynamic update I know. The problem is when multiple people are using the Qlikview document it locks up. Is there a way to prevent the lock up or more likely an alternative method to do these calculations?   

Background:

The Qlikview dashboard is meant to be a "what if" analysis tool for our inventory team decide which locations to stock product. We are an ecommerce site dealing with heavy product, so minimizing shipping distances helps reduce freight cost (which is charged to the customer) and thus increases conversion. But the isn't necessarily viable for every product to be in every location.

Problem:

I’m giving our analysts the ability to turn on and off locations in Qlikview, and then having Qlikview recalculate some demand metrics for the new closest ACTIVE warehouse based on a pre-calculated distance rank. I have a field that is a flag showing if a location is active or not, I use a trigger and dynamic update to set that flag to active or inactive on a selection. This works but when deployed on a server (AJAX), multiple people using it causes the document to lock up. I believe this is because everyone is fighting to update a value in a shared data source.

I’m trying to find an alternative to using dynamic update to mark locations as active or inactive. I met with someone at Qlilkview and he suggested using variables. This would mean having a variable for every SKU and warehouse combination, which would be 100,000 variables. This might be too resource heavy, so he also recommended challenging the Qlkiview community for a more efficient solution.  

Some extra details:

• I’m running Qlikview 11 server with publisher, users access Qlikview through AJAX
• Most of the calculations run off a variation of this formula:

FirstSortedValue( {<ActiveWarehouseFlag  = {1} >} ShoppingCartTotal, DistanceRank)

It uses set analysis to see what locations are active for a SKU, then returns the value for first ranked location, meaning the closest active location for that shopping cart.

• We do these calculations for every shopping cart, so there is a one-to-many relationship for each cart to all possible locations. The distance ranks are pre-calculated in SQL.
• An example of the Dynamic update script:

$(=Concat(distinct  'UPDATE ActiveWarehouses SET ActiveWarehouseFlag = ' & $(varWarehouseToggleSwitch) & ' WHERE ActiveWarehouses.ProductItemID = ' &  ProductItemID & ' and ActivationFromZip = ''' & $(=chr(39) & aggr(FirstSortedValue( ActivationFromZip, DistanceRank), SCitemID) & chr(39) ) & chr(39)  , ';'))

It uses concat() to create a list statements for every SKUs and warehouse combination current selected, and sets the on/off flag appropriately

Any ideas on a better way to toggle the SKU/locations on and off would be a great help. If any more details are needed please let me know.

Thanks in advance!

[Oleg_Troyansky]

Looks like one direction for you would be to guard the "one at a time" scenario - introduce some flags that would only allow a single user to be messing with the common data at a given time.

The other possible venue to explore is using INPUTFIELD - in a way, it's like using variables (as your QlikTech advisor suggested), only instead it's a field that you can link to your Location table.

INPUTFIELDs are stored per user, so multiple people can play their What-If games at the same time.

Watch the data volumes, since those things tend to grow quite a bit. You might want to limit the audience for this type of analysis. For example, if 100 people need general inventory analysis, and 5 people need the What-If logic, don't let them all access the same document - divide your document into "generic" and "What-If" and split the audience.

good luck!

Oleg Troyansky

[#3]

Thanks Oleg,

I think your correct about how to distribute and limit access to the document.

When I initially developed the document I started with INPUTFIELDs, but to my knowledge there is no way to adjust them with an action/trigger. This means I would need users to enter a 1 or 0 to toggle locations on and off. This does work but much less user friendly than what I got from dynamic update and triggers which allows users to just click to toggle on/off.

If I can use a trigger to alter the INPUTFIELD so I can attach it to an on click trigger, it would be the perfect solution. Is anyone awre of that kind of functionality?

[#4 · Accepted solution]

I ended up finding an alternative solution to using Dynamic Update. Instead I used alternate states, and defined "active warehouses" as where selections intersect (instead of changing a datafield as a flag). So the alternate state, lets call it [ActiveState], contains all my active locations as its selections. The set analysis { $ * [ActiveState]} would return the locations enabled. A few Toggle Selection actions in triggers allow users to set locations on and off.

This gave a big performance boost and also allowed for users to save their set ups in bookmarks.