Using a variable created with set analysis in an aggregation function

[CamisLima]

Hi! I started working with Qlik Sense this week, I made some courses to understand the basic functions of the application but could not found a solution for my problem. I hope someone could help me in this situation 🙂

I have a phisical table with these fields:

•  ENTITY (TX_ENTY)
• CLUSTER (TX_CLUS)
• CLUSTER PRICING (TX_CLUS_PRIC)
• MARKET (TX_MKT)
• COMPANY (TX_SOUR)
• FARE BASIS (TX_FARE_BASI)
• OBSERVATION DATE (IN_DIA_OBSV this field has values [0, 1, 7, 28, 364] to makes easy relation between the dates)
• PRICE (FN_PRIC)

It is possible to filter the data based on observation date: today [IN_DIA_OBSV = 0], yesterday [IN_DIA_OBSV = 1], a week ago [IN_DIA_OBSV = 7], a month ago [IN_DIA_OBSV = 28] and last year [IN_DIA_OBSV = 364].

I have created variables [e.g. VALOR_D0_MIN] that calculates the minimum value for each combination:

Min(
	{$<IN_DIA_OBSV={0},RANK_PRIC={'1'}>}
    TOTAL <TX_ENTY,TX_CLUS,TX_CLUS_PRIC,TX_MKT,TX_SOUR,TX_FARE_BASI>
    FN_PRIC
    )

Then I have created variables [e.g. VAR_D1_MIN] that compares values from today [IN_DIA_OBSV = 0] with the values from the other dates. If the value exist today but is not found on the other day the value of this variable is 'CREATED', if the value did not exist today but exist on the other day the value of this variable is 'DELETED', and if the value exist on both dates makes ((VALUE_TODAY - VALUE_OTHER)/VALUE_TODAY):

IF(ISNULL($(VALOR_D0_MIN)) = -1,IF(ISNULL($(VALOR_D1_MIN)) = -1, NULL, 'DELETED'),
IF(ISNULL($(VALOR_D1_MIN)) = -1,'CREATED',(($(VALOR_D0_MIN)-$(VALOR_D1_MIN))/$(VALOR_D0_MIN))))

The final step for this analysis is to count how many 'DELETED', 'CREATED', VALUES ABOVE ZERO and VALUES BELLOW ZERO are in those variables. This value have to change with the selections made in my app.

I have tried:

COUNT({$<VAR_D1_MIN = {'DELETED'}>} TX_FARE_BASIS)

But the app calculates all without the filter of VAR_D1_MIN ='DELETED' or the answer is a null field.

[Gysbert_Wassenaar]

In set analysis <MyFieldName = {'ValueX'}> is called a set modifier. In a set modifier you can only use field names on the left side of the = operator. A variable that contains only a field name can also be used, because when the variable is evaluated it will be replaced with its value which is a field name.
What you have in your variable is the text string of an expression. That's not a field name. So what you get is something like <Min( ....... ) = {'DELETED'}>. Which is rubbish that Qlik Sense can't process.

How to solve your problem? Do you have a field that uniquely identifies the records in your table? If not create one. If you have one, e.g. MyID, you can try using that in your set analysis expression:

Count( {<MyID={"=$(VAR_D1_MIN)='DELETED' "}>} TX_FARE_BASIS)

[CamisLima]

Hello, Gysbert. Sorry for the delay, was a period of holidays here.

First of all, thanks for the explanation about Set Analysis, it has been a little difficulty to understand the exactally way the QlikSense works with it.

As you have sugested I have created a new column of MyID, but my Set Analysis only worked as expected when I added an aggregation function on it:

Count(
{$<MyID={"=$(VAR_D1_MIN)='DELETED'"}>}
AGGR(TX_ENTY,TX_CLUS,TX_CLUS_PRIC,TX_MKT,TX_SOUR,TX_FARE_BASI)
)

Another problem that I'm having with this is that my variable has four distinct values possible: DELETED, CREATED, INCREASE (for values above zero), DECREASE (for values bellow zero); when I try to count lines with value DELETED or CREATED it works as expected, but when I try the same for INCREASE or DECREASE the value resulted is the total lines of my data. Any guess about what could be happening?

[Gysbert_Wassenaar]

Can you post a small example app that demonstrates the issue?