Multiple lines in a chart depending on expression value

[#1]

I'm not really sure how to word this but is it possible to have a chart (I guess it would be a chart), that shows on a separate line for each, a summary for values based on a calculated value? What I would like to do is this: I have an expression called "Average Investment" which is a sum of items on hand over a time period (something like this sum(item_on_hand_value) / NumOfMonths). And I have another value called "Annual Turns" which shows how much an item turns in inventory over the same time period (((sum(item_cost) / NumOfMonths) / [Avgerage Investment])). What I would like to do is break down the average investment amount by annual turns. My chart (?) would look something like this:

Total Average Investment $100,000

Less than 1 Turn 5,000 5%

1 - 1.9 Turns 35,000 35%

2.0 - 3.9 Turns 45,000 45%

4 or more Turns 15,000 15%

I assume my dimension will have to be the calculation for Annual Turns. How do I put the breakdown on separate lines? Is something like this possible?

Thanks in advance.

[johnw]

Can you give us some sample data for which you want to see those results? The only way I can verify any solution is by trying it out with some sample data and seeing if I can produce your desired results. Probably better that you provide the data than I invent my own. 🙂

Also, confirming something about your calculations:

Annual Turns = (((sum(item_cost) / NumOfMonths) / [Average Investment]))
= (sum(item_cost) / NumOfMonths) / (sum(item_on_hand_value) / NumOfMonths)
= sum(item_cost) / sum(item_on_hand_value)

Correct?

[#3]

Hi John,

I've attached a file that has some sample data. I would like the result to look something like this:

Total Average Investment $1527.50

< 1 Turn 0.00

1 to < 2 Turns 900.00

2 to < 3 Turns 0.00

3 to < 4 Turns 562.50

4 and >4 Turns 65.00

Also, the Annual Turns calculation is really:

Annual Turns = ([COGS] / NumOFMonths) * 12) / [Avg Investment]

= (sum(Sale_Cost) / NumOfMonths) * 12) / [Avg Investment]

= (sum(Sale_Cost) / NumOfMonths) * 12) / (sum(On_Hand_Value) / NumOfMonths)

I forgot to annualize the average investment earlier.

Thanks.

[johnw]

OK, here are two working approaches and one that maybe could be debugged but isn't working yet. Both working approaches go ahead and load the possible ranges in field Annual_Turn_Range of table Annual_Turn_Ranges. One of the working approaches goes on to calculate everything in the script such that the chart is a simple sum. The other working approach does the remaining calculations in the chart instead. The approach that isn't working yet was to use a calculated dimension in the chart as well, so that it wouldn't require any script changes at all. Hopefully you can pull it up OK. If not, I might need to rebuild it in 8.5. I didn't notice that I was in 9.0 when I built it.

[#5]

Hi John,

Thanks for your reply. I was also trying to do it with a calculated dimension in the chart but was having troble getting it to total. I like the second chart you did that builds the turn levels in the script and only does the remaining calculation in the chart. The problem I am running into though is the time frame can be changed by the user (the Date_Value field). When that happens the NumOfMonths value changes which can change the Annual Turns value. An item may have 3.2 turns when all four months are selected but 4.5 turns when 2 months are selected. With your chart that item doesn't appear anymore in any of the turns.

Look at item "12345". When you have all 4 months selected the average investment = $562.50 ((500 + 400 + 700 + 650) / 4. And the Annual Turns = 2.8 (((50 + 100 + 50 + 100 + 200 + 25) / 4) * 12) / 562.50.

But if you select months 2/28/09 and 3/31/09 the average investment = =$550 (400 + 700) / 2. And the Annual Turns = 3.82 (((50 + 100 + 200) / 2) * 12) / 550.

Is there a way we can do this without calculating the annual turns in the script?

I really appreciate your help. Thanks.

[johnw · Accepted solution]

Hah! I've been scratching my head over this, and I think I finally figured it out. It turns out that having BOTH approaches in one application was messing it up. I should have made two applications. Also turns out that I had one minor error in the second approach that the extra script was covering up, which is that I also needed to aggregate by Annual_Turn_Range.

So is this what you wanted?

[#7]

This looks like it should work. I think I have a little tweaking to do with my actual data. I may have other questions as I get into it more.

Thanks for all your help!

[#8]

I was able to get this to work with my data with one caveat. There are occasions when the annual turn is negative. Those values aren't being accounted for in the chart expression "Avgerage Investment" I'm assuming due to the "rangemin(floor" function. Is there a way to account for those <0 values? I've attached a modified example of your last example that includes a <0 value.

Thanks again.

[#9]

Nevermind. I figured out I just need to add another "if" statement checking if the value is <0 to treat it as 0 otherwise continue with the rangemin(floor) functions. It appears to work correctly.

Thanks again for all your help.