Getting possible mix of total

chiso_chiso — Wed, 03 Feb 2016 06:24:38 GMT

Gurus,

I have 3 variables (Sales, Agents and Days) to work out Sales per Agent (SPA).

SPA = (Sales/Agents)/Days

Example: (100Sales/5Agents)/10days = 2SPA

Is there a possible work around to start from SPA and get possible mix of the Variables(Sales, Agents and Days)

Example of possible mix:

Sales Agents Days SPA

100 50 1 2

50 100 1 2

100 25 2 2

etc

Please Assist

Re: Getting possible mix of total

Wed, 03 Feb 2016 07:06:38 GMT

The problem I believe with your question is to which end do you define a begin and finish.

You can create iteration loops in script by using a for each var in fieldvaluelist('field1') nested in each value dimension you want to evaluate the permutations. With a where clause you can restrict the results to give you only those that match a given output

However, how do you define how the var for each value in fieldlist is incremented. In your example, originally you had 5 agents, then in your example mix you have 50, 100, 25. This does not follow a pattern.

If I had to design this I would let SPA and sales stay the same. Since only leaving SPA the same would result in an infinite number of possibilities. Then I would need to cap Agents and Days as this could also scale up infinitely. At the lowest possible level to complete, we should define that a day is the quickest, and not half a day for instance, so by default, sales / spa would give us our max agents or days (i.e. 100/2 = 50). Sales also would determine the ratio of days and agents required to deliver the same sales target at the same rate. Here's an example application of this with all permutations.

topic Getting possible mix of total in QlikView

Getting possible mix of total

Re: Getting possible mix of total