10 Replies Latest reply: Sep 2, 2011 10:31 AM by Stefan Wühl RSS

    Cost Index calculation problem

    Eliran Sanor

      Hi all,

       

      I'm intrested in calculate cost index for products.

      The forumla is pretty simple,

      X=avg price 2011 * qty 2011

      Y=avg price 2010 * qty 2011

      Cost Index=(X-Y)/X

       

      My problem is as following, If the quantities are equal to 0 (In 2010 or 2011), I don't want it in my caluclation.

      The calculation should be dynamic when changing the selected years, so Set analysis will be used.

      Another thing, because it's dynamic I can't think of a flag I can use (In the load script) with the set analysis.

      Any help will be welcomed.

      Attached is a sample of my problem.

       

      Regards,

      Eliran.

        • Re: Cost Index calculation problem
          Stefan Wühl

          Hi Eliran,

           

          please have a look at attached.

           

          I replaced the fixed years in set analysis with get the max(year) resp max(year)-1 from the selection and checking with an if()-clause if sum(qty) is larger than zero.

           

          As you see, the entries with missing qty are removed from the table.

           

          Hope this helps,

          Stefan

            • Cost Index calculation problem
              Eliran Sanor

              Hi Stefan,

               

              First of all, thanks for your help.

              Second, My main problem is the calculation itself more then the presentation of it in the table (altought it's important).

              If you can see in the attached file you sent, sum of column X isnt equal to the sum of rows.

              That's because the calculation doesn't refer to the condition you inserted in the column.

               

              Any thoughts on that issue?

               

              Regards,

              Eliran.

                • Re: Cost Index calculation problem
                  Stefan Wühl

                  Hi Eliran,

                   

                  sorry for that, I thought that expression total would be doing the job, but here I was totally wrong (because I checked a sum of Qty > zero which then always true in a complete table with all / many products and takes all values again into acount).

                   

                  Please check attached.

                   

                  - Change expression total to sum of rows for X/Y column

                  - changed index calculation column to an advanced aggregation

                  - check in Excel, that calculations are correct on row level and in total

                   

                  Regards,

                  Stefan

                    • Re: Cost Index calculation problem
                      Eliran Sanor

                      Hi Stefan,

                       

                      I can't thank you enough..

                      The solution worked perfectly!

                       

                      Thanks and regards,

                      Eliran.

                      • Re: Cost Index calculation problem
                        Eliran Sanor

                        Stefan,

                         

                        One more thing if you may.

                        I want to view the changes in the cost index by Yearly quarters but it's acting little funny when I do it.

                        Is it the set analysis or the aggr that is interfering?

                        Please find attached the improved example.

                        I really appreciate your help.


                        Regards,

                        Eliran.

                          • Cost Index calculation problem
                            Stefan Wühl

                            Eliran,

                             

                            I had a quick look, but don't find a problem yet. What do you mean with acting little funny.

                             

                            I added again the two columns for X / Y sums and temporarily also product as second dimension.

                             

                            The sums seems ok to me, also the cost index calculation ( I double checked in Excel).

                             

                            Note that in your sample, the total cost index is not the average of the quartely indices. The cost index is always based on the sums X/Y which widely vary over time, so there is a weigh we have to take care of.

                             

                            Could you point me to the problem?

                             

                            Regards,

                            Stefan

                              • Cost Index calculation problem
                                Eliran Sanor

                                Stefan,

                                 

                                When I'm looking at the Cost Index of 2011-Q1, When the year is checked I get -7.52%, if I remove the selections from the Year dimnesion, I get for the same quarter -54.79%.

                                It's acting even funier in my real DB where I miss out quarters when not selecting a specific year while viewing the data by Yearly Quarters. For example, 2010-q3,2009-q3,2009-q4, 2008-q4.

                                Now, 2008 I can blame with lack of data because it's the first year with data in my DB, but it still shows cost index for 2008 quarters which is very bizzare.

                                Same goes for the example, 2008 quarters calculations shouldnt be shown at all, since there is no data for 2007.

                                Any idea?

                                 

                                Thanks for the help.

                                 

                                Regards,

                                Eliran