4 Replies Latest reply: Dec 27, 2011 8:54 AM by washington alex RSS

    ValueList x Set Analysis

    washington alex



      I am using this function:                                                                             



      = IF(ValueList('Atual', 'Anterior','Variacao_Vertical') = 'Atual',

      sum( {$<MES_ANO ={'$(vMesAtual)'}>} DISTINCT VALOR_PEDIDO),

      sum( {$<MES_ANO ={'$(vMesAnoAnterior)'}>} DISTINCT VALOR_PEDIDO))


      I'm finding it very heavy.


      Is there any way to add the "valuel" within the SetAnalysis?



      sum( {$<MES_ANO ={'$(vMesAtual)'}, ValueList = {'Atual'}>} DISTINCT VALOR_PEDIDO )

        • ValueList x Set Analysis
          Sunil Chauhan

          use this

          sum({<MES_ANO ={$(vMesAtual)}, ValueList = {'Atual'}>} DISTINCT VALOR_PEDIDO )

            • ValueList x Set Analysis
              washington alex

              It did not work. The word "valuel" is highlighted in red.

                • Re: ValueList x Set Analysis
                  Miguel Angel Baeyens de Arce



                  ValueList cannot be used in set analysis that way. Instead, I'd create a new field in the script that equals to your ValueList() values, for example


                  LOAD Chr(64 + Ceil(Rand() * 3)) AS ID,
                       Date('01/01/2010' + Ceil(Rand() * 730)) AS MasterDate,
                       Ceil(Rand() * 1000) AS Amount
                  AUTOGENERATE 20;
                  LOAD Min(MasterDate) AS Min_MasterDate,
                       Max(MasterDate) AS Max_MasterDate
                  RESIDENT Data;
                  LET vMinDate = FieldValue('Min_MasterDate', 1);
                  LET vMaxDate = FieldValue('Max_MasterDate', 1);
                  LOAD Date($(vMinDate) + RowNo() -1) AS MasterDate,
                       If(Year(Date($(vMinDate) + RowNo() -1)) = Year(Today()), 'Current', 'Last') AS YearName
                  AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;


                  Where YearName has this year, previous year and so on, and will be used as the dimension instead of the ValueList. In this case, I'm using only date dimensions (current year, last year...) but you can use several fact tables concatenated with a new field each of these tables to show their source (current, last, budget...)


                  Hope that helps.