Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

ValueList x Set Analysis

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 )

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

4 Replies
SunilChauhan
Champion
Champion

use this

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

Sunil Chauhan
Anonymous
Not applicable
Author

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

Miguel_Angel_Baeyens

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

Anonymous
Not applicable
Author

Thank you.

I think this solves the problem.