Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 )
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
use this
sum({<MES_ANO ={$(vMesAtual)}, ValueList = {'Atual'}>} DISTINCT VALOR_PEDIDO )
It did not work. The word "valuel" is highlighted in red.
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
Thank you.
I think this solves the problem.