4 Replies Latest reply: Sep 19, 2017 8:51 AM by Jonathan Dienst RSS

    How add set analysis to this expression

    yusuuf rawat

      The expression below works correctly and displays the data for the selected year by the user correctly

       

      if([FIN JAAR]=$(MAKS_JAAR), Sum(if(Aggr(Sum( TOTAL <PLU> BW),PLU)<0,Aggr(Sum(TOTAL<PLU> BW),PLU))))

       

      How do i add another expression to show the same data as above for the previous year , i tried this but it doesnt work

      Sum(if(Aggr(Sum({< [FIN JAAR]={$(y_previous_year)}> TOTAL <PLU> BW),PLU)<0,Aggr(Sum({<[FIN JAAR]={$(y_previous_year)}>} TOTAL <PLU> BW),PLU)))

       

      also

       

      if([FIN JAAR]=$(_previous_year)..... ect wont work , because the year the user selects is the maximim year , i think you have to add it into set analsysis for the previous year

       

       

        • Re: How add set analysis to this expression
          Jonathan Dienst

          I suspect you are over-complicating the expression. Why do you need an Aggr()? I would try

           

          For the current year:

          =Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} TOTAL <PLU> BW)

           

          For the previous year

          =Sum({<[FIN JAAR]={$(=MAKS_JAAR - 1)}>} TOTAL <PLU> BW)

           

          Assumes that MAK_JAAR is a variable containing a year (like 2017) and that the users are making a selection on FIN_JAAR (and the a selection in FIN_JAAR modifies the value of MAKS_JAAR).

            • Re: How add set analysis to this expression
              yusuuf rawat

              The reason for the Aggr() is that in the granular data people scan the barcode in the price column by mistake, and they cancel it out with a -price equal to the barcode = -1626236363563653 +1626236363563653 +300

              so the 1626236363563653 pair is the illegitime numbers and the +300 is the right number ,sometimes there are legitimate negitive numbers .

               

              I aggr the sum per invoice to avoid the illiegitimate pair , but know i only want to show legitimate  negitive numbers .

                • Re: How add set analysis to this expression
                  Sunny Talwar

                  May be this

                   

                  If(Only({<[FIN JAAR] = {$(y_previous_year)}>} [FIN JAAR]) = $(_previous_year), Sum({<[FIN JAAR] = {$(y_previous_year)}>} If(Aggr(Sum({<[FIN JAAR] = {$(y_previous_year)}>} TOTAL <PLU> BW),PLU) < 0, Aggr(Sum({<[FIN JAAR] = {$(y_previous_year)}>} TOTAL <PLU> BW), PLU))))

                  • Re: How add set analysis to this expression
                    Jonathan Dienst

                    Its not clear to me how the Aggr() helps you. Simply summing the values would cause the invalid entry to be cancelled by its reversal. Perhaps it would be clearer if you posted a small sample qvw. It does not have to have real data, but the sample data should illustrate the structure of the real data.

                     

                    Note that an Aggr() respects the current set and is affected by user selections and set expressions. If you want previous year's data you will need to set the year override on the outer sum (like Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} ... ) as well as the inner ones.Something along these lines

                     

                    Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} Aggr(Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} ... )....)