11 Replies Latest reply: Feb 27, 2012 7:12 AM by benjmoff RSS

    Help with MAX

      Hello,

       

      I have an expression that shows me what the Max sales qty of a product was in a period of time.

       

      max({$<[Barcode Stock] = >}aggr(sum({$<[Barcode Stock] = ,[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}>}[Qty Stock]),[Barcode Stock],[Rolling Period Stock]))

       

      this works fine and returns the max qty of sales in the best period.....

       

      what I want is an expression to show me which period the sale was in....

       

      example:

       

      PeriodSum of Qty

      1

      15
      210
      325
      424
      520
      616

       

      so my expression would tell me the max sales qty was 25, and i'm after an expression to tell me the period was 3.

       

      Thanks,

       

      Ben.

       

        • Re: Help with MAX
          j i

          Hi, here it is. Let me know if you need anything else

          • Re: Help with MAX

            Pls try this one ....

             

            sum({$<[Sum of Qty]={$(=max([Sum of Qty]))}>}Period)

             

             

            Regards,

            Antony.

            • Help with MAX

              Hi thanks for your replies.

               

              have tried both but no luck as of yet.

               

              the Sum of Qty is from this expression

               

              max({$<[Barcode Stock] = >}aggr(sum({$<[Barcode Stock] = ,[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}>}[Qty Stock]),[Barcode Stock],[Rolling Period Stock]))

               

              so there are multiple lines that need to be calculated to get the total max sales qty in each period. see below....

               

              PeriodQty
              15

              1

              10
              210
              35
              310
              310
              424
              520
              616

               

              so in Period 3 we had the max sales of 25....

               

              thanks again,

               

              Ben.

                • Help with MAX
                  j i

                  Ben, did you check my application? Please download it, go to  Settings --> Variable Overview, and where it says "a" paste your expression max({$<[Barcode Stock] = >}aggr(sum({$<[Barcode Stock] = ,[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}>}[Qty Stock]),[Barcode Stock],[Rolling Period Stock]))

                   

                  OF course this should be done directly in your application but make sure to follow the logic in my application.

                    • Help with MAX

                      Hi,

                       

                      i have done this and it gives me a period but it is only right a very small percentage of the time....

                       

                      it seems to work when the max sales are in the 1st month in the data..... but if the max is for example in period 10 but i have sales in periods 1, 2, 3, 4, 5, 6...... it shows the period as 1

                       

                      thanks,

                       

                      ben.

                        • Help with MAX
                          j i

                          If it doesn't work is because your "max" formula is wrong. Please check your formula or upload your application so we can check it. We can't guess and work with "ghost" fields we don't see