4 Replies Latest reply: Jan 22, 2016 9:53 AM by William Lima RSS

    Problems with set analisys

    William Lima

      Hi everybody,

       

      I need a little help.(Sorry, my English is horrible).

       

      I'm trying to sum with that conditions : if(sum( {<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0),0,1)

       

      That expression means that, with I have a sale in the last year, in the same time(month, day depend of the user selection), of the current year, I need to sum that value.

       

      This expression work, when I put "0" or "1", but don't work when I try to sum.

       

      Please, someone can help me?

        • Re: Problems with set analisys
          Stefan Wühl

          You can't just embed a Sum() into another Sum() without advanced aggregation (aggr() function).

           

          In what context are you using this expression? Client?

           

          Then maybe like this:

           

          Sum( {<Client = {"=sum( {<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0"}, ANO={$(=YEAR(DATA_INI))} >} valor)

            • Re: Problems with set analisys
              William Lima

              Hi Swuehl, thanks for your answer and your attention, but actually the problem isn't it the sum, because when I put:

               

              if(sum( {<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0),sum( {<ANO={$(=YEAR(DATA_INI))}>} valor ),0)

               

              the pivottable shows exactly that I want. The problem is the subtotal of the pivot table that shows the sum of all the sum, not only with the conditions. The total of the sum with conditions need to be less than the total. I try to make a sentence where the logic is, when the outlet have in the last and the current year,month and day sales, I sum the value. Because , I can tho close a outlet in the last year and because that I can't compare.

                • Re: Problems with set analisys
                  Stefan Wühl

                  You need to use adavanced aggregation to evaluate your expression in the context of your outlets even when calculating a partial sum:

                   

                  Sum(

                  Aggr(

                  if(sum( {<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0),sum( {<ANO={$(=YEAR(DATA_INI))}>} valor ),0)

                  , YourOutletField

                  ))

                   

                  Instead of YourOutletField, use the dimension fields you are using in your pivot table chart (outlet, as far as I understand).