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 Kumar 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



        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.