topic ValueList x Set Analysis in QlikView

ValueList x Set Analysis

Anonymous — Tue, 27 Dec 2011 11:41:56 GMT

hello.

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

SunilChauhan — Tue, 27 Dec 2011 11:45:42 GMT

use this

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

ValueList x Set Analysis

Anonymous — Tue, 27 Dec 2011 12:10:48 GMT

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

Re: ValueList x Set Analysis

Miguel_Angel_Baeyens — Tue, 27 Dec 2011 12:24:02 GMT

Hi,

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

Data:
LOAD Chr(64 + Ceil(Rand() * 3)) AS ID,
     Date('01/01/2010' + Ceil(Rand() * 730)) AS MasterDate,
     Ceil(Rand() * 1000) AS Amount
AUTOGENERATE 20;
MaxMinDates:
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);
Calendar:
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.

Miguel

ValueList x Set Analysis

Anonymous — Tue, 27 Dec 2011 13:54:17 GMT

Thank you.

I think this solves the problem.