4 Replies Latest reply: Jun 20, 2016 6:03 AM by Ashvin Ramiah RSS

    Stock Trend Analysis

    BHARATI SOOPAL

      Hi Team,

      I am trying to display stock trend analysis for a particular item as follows:

       

      stock_Trend_Analysis.png

       

      Filter Year : 2016

      Find below expression to calculate same:


      stock_trend_expression.png

       

       

      Jan 2016 > it should take all data from stock where transaction date <=31/01/2016

      Feb 2016> where transaction date <=28/02/2016

      Mar 2016> where transaction date <=31/03/2016

      …….

      …….

      Etc

      Grateful if you could kindly advise as above is not working up to my expectation.



      Thanking You,

      Bharati.


        • Re: Stock Trend Analysis
          Sunny Talwar

          When you say is not working up to your expectation, what is the exact issue you are facing? Error, 0, null, numbers different then expected?

           

          Alternatively, try this:

           

          If(GetSelectedCount(STOJOUCurMonth) = 1, Sum({<STOJOUCurMonth, YearField, QuarterField, MonthYearField, [Transaction Date] = {"$(='<=' & Date(Max([Transaction Date]), 'DD/MM/YYYY'))"}>} Quantity) , Sum(Quantity)

           

          Add all date related fields (in red above) in your set analysis to ignore any selections in them, because any selections you want to make will flow through using the Transaction Date.

           

          Also note that I have not added [STOJOU Item Reference] = {'RM030'} as I was not sure if that was required or not, but feel free to add this wherever you think that it is necessary

          • Re: Stock Trend Analysis
            Ashvin Ramiah

            1. get 2 variables:
             

             

             

            2. in Measure use 

                  Sum({<StockDate = {">=$(vFrom)<=$(vTo)"} >} valueAmended )

              • Re: Stock Trend Analysis
                Ashvin Ramiah

                Get CurMonth = month(YourDateField) in Load scipt

                 

                ---------------------------

                below will display past 12 month Sum

                If(GetSelectedCount(CurMonth) = 1 ,

                                  

                               sum(    {<    

                                  CurMonth = {"$(= monthname(Addmonths(CurMonth,-1)))",

                                            "$(= monthname(Addmonths(CurMonth,-2)))",

                                                        "$(= monthname(Addmonths(CurMonth,-3)))",

                                                        "$(= monthname(Addmonths(CurMonth,-4)))",

                                            "$(= monthname(Addmonths(CurMonth,-5)))",

                                                        "$(= monthname(Addmonths(CurMonth,-6)))",

                                                        "$(= monthname(Addmonths(CurMonth,-7)))",

                                            "$(= monthname(Addmonths(CurMonth,-8)))",

                                                        "$(= monthname(Addmonths(CurMonth,-9)))",

                                                        "$(= monthname(Addmonths(CurMonth,-10)))",

                                            "$(= monthname(Addmonths(CurMonth,-11)))",

                                                       // "$(= monthname(Addmonths(CurMonth,-12)))",

                                                        "$(= monthname(Addmonths(CurMonth,0)))"

                                                                                  

                                            } , trxType={'myType'} 

                                        >}

                                       Balance )/12    // this is the Avarage for SalesValue

                                      

                                  /    // this is divison

                                 

                                  sum(    {<    

                                  CurMonth = {"$(= monthname(Addmonths(CurMonth,-1)))",

                                            "$(= monthname(Addmonths(CurMonth,-2)))",

                                                        "$(= monthname(Addmonths(CurMonth,-3)))",

                                                        "$(= monthname(Addmonths(CurMonth,-4)))",

                                            "$(= monthname(Addmonths(CurMonth,-5)))",

                                                        "$(= monthname(Addmonths(CurMonth,-6)))",

                                                        "$(= monthname(Addmonths(CurMonth,-7)))",

                                            "$(= monthname(Addmonths(CurMonth,-8)))",

                                                        "$(= monthname(Addmonths(CurMonth,-9)))",

                                                        "$(= monthname(Addmonths(CurMonth,-10)))",

                                            "$(= monthname(Addmonths(CurMonth,-11)))",

                                                       // "$(= monthname(Addmonths(CurMonth,-12)))",

                                                        "$(= monthname(Addmonths(CurMonth,0)))"

                                                                                  

                                            }  , trxType={'Sales'}

                                        >}

                                       Balance)/12

                                  

                            ,

                           

                           

                           

                sum(Balance)

                 

                 

                 

                 

                 

                 

                )