4 Replies Latest reply: Aug 18, 2016 8:11 AM by Владимир Фасюра RSS

    In one dimension, the amount of pull of the maximum sum value of another dimension.

    Владимир Фасюра

      Hi!

       

      Sorry for Google Translator

       

      There is a Period (month, roughly speaking, 201405, 201406, 201407, etc.).

       

      there name Products.

       

      there are Sales

       

       

      very simple. - only 3x fields.

      two measurements (Period and Products ... moreover period should be skipped) and amount.Sales  .

       

      without modifying the script loading, table need  calculate  the product last a significant amount of sales per month. you can  compare it with the remains of the end of the month (instead of simply "max" is "the SUM".. In some product this "remains" or "Balance" was in January. Someone in March. .itd.

       

       

      I thought that it is easy to do, just setting in the measurement of a single field "Products", and write the expression:

      sum ({<Period = { '$ (= max (Period))}>} Sales)

      but it was not there. $ (= Max (Period)) is interpreted as the maximum period generally in the document. . accordingly, many products will be on zero balance on this very last month. and I need to on their last month .. tried to shove the expression just = max (period) .. months displayed normal (if for some reason, empty values also found to be significant, then max (if (Sales> 0,Period)), but just trying to insert a summation formula - it does not work ..

       

      so i need 2 rows (or two columns)  -       Period----LastSales

      thank you in advance

       

      I corrected the message and translated into English .. but my updated version the next day gone. What does it mean? I am outraged by this fact...
      many details have been written

       

      Сообщение отредактировано: Владимир Фасюра

        • Re: In one dimension, the amount of pull of the maximum sum value of another dimension.
          Sunny Talwar

          Can you try this:

           

          FirstSortedValue(Aggr(Sum(Sales), Period, Product), -Aggr(Period, Period, Product))

            • Re: In one dimension, the amount of pull of the maximum sum value of another dimension.
              Владимир Фасюра

              thanks for the answer.

              But this method, I also tried.

               

              Same result as sum ({<Period = { '$ (= max (Period))'}>} Sales)

               

              Visible only those products for which the period is the maximum..

               

              for example, sales are 10 products in the last month.

              Here it will be shown on the data in your example..

               

              But the data will not be displayed on products, whose sales were 3 months ago .. 4 months ago. etc

               

              I tried a variety of methods.

              I am satisfied with the period that is shown when I enter into a separate column expression =max(Period) ..  without "$"

               

              for example.. "Apple"   last Sales at 201205  10000$

               

              =max(Period)   will be ‘201205’

               

              but when I try to put this condition in the expression sum ({<Period = { '$ (= max (Period))'}>} Sales) The result is 0$,

               

              Because =$ (= max (Period))   ...         = 201412  (its max date of Data table)

               

              in your example, the results are exactly the same +(

                • Re: In one dimension, the amount of pull of the maximum sum value of another dimension.
                  Sunny Talwar

                  Set analysis is evaluated once per chart. When you do Max(Period) in the chart, it calculates the overall max and not max by your dimension. Would you be able to share you input file or some dummy data to show how this can work?

                    • Re: In one dimension, the amount of pull of the maximum sum value of another dimension.
                      Владимир Фасюра

                      1st - the problem is solved!!

                       

                      now - explanation:

                       

                      when I realized that I can not quickly solve the problem, I have prepared a dummy-file to find the right solutions on low-size data. and then all calculations are carried out exactly in it..

                      and at this moment I somehow incorrectly uploaded data to that dummy from test file..   so.. all cells uploaded as non-"null" values.

                      so FirstSortedValue(Aggr(Sum(Sales), Period, Product), -Aggr(Period, Period, Product))   not worked properly

                       

                      today:

                      I'm was going to send you dummy-data, but decided to first basically check  my primary work-data file

                       

                      And it worked!

                       

                      As it turned out, I had already used the same expression, which you gave to me(in test-file) .. but I forgot that i need to use in test-file  the condition  if(Sales>0,Period)

                       

                      so..  for corrupted data useful the following code

                      FirstSortedValue(Aggr(Sum(Sales), Period, Product), -Aggr(if(Sales>0,Period), Period, Product))

                       

                      last question: i cant understand how to share non-commercial examples of qlickview apps(i mean without "attempt to restore")  =(..

                      so.. *csv   and *.xls  to share (in next tasks if needed)