8 Replies Latest reply: Sep 15, 2009 12:01 AM by John Lewis RSS

    Multiple lines in a chart depending on expression value

      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.

        • Multiple lines in a chart depending on expression value
          John Witherspoon

          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?

            • Multiple lines in a chart depending on expression value

              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.

                • Multiple lines in a chart depending on expression value
                  John Witherspoon

                  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.

                    • Multiple lines in a chart depending on expression value

                      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.