topic Re: Set analysis to variables in QlikView

Set analysis to variables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I need to use a variable and filter it with set analysis (in a pivot table) and I cant come to how to do it.

The variable is the calculation of a metric(Performance), all the fields of a particular dimension (Product Line) have one vale of the metric associeted. What i need is to calculate the average performance for 3 specific product lines (A , B , C), the 3 product lines together must be represented with 1 single name 'Total GSS' which its performance= avg(performance A , B, ,C ).

The variable is the following:

vCSAT=sum(TCE_RSLT_METRIC_CSAT_FLAG*if(vWithWeights=1,TCE_RSLT_WEIGHTS,1))/sum(TCE_RSLT_METRIC_CSAT_DSAT_TOTAL_FLAG*if(vWithWeights=1,TCE_RSLT_WEIGHTS,1));

As a dimension I am using:

'GSS'

expression:

// This is just a tryal, that is why i am only tring to filter by 1 Product Line. Nevertheless the objective is the descrived above.

avg({$<Product Line='A'>} $(vCSAT))

Thanks a lot for you time and help.

If something is not clear just ask it and i will answer asap.

Regards,

Lluís ^^

Re: Set analysis to variables

MK9885 — Tue, 10 Oct 2017 18:24:26 GMT

avg({$<Product Line={'A'},TCE_RSLT_WEIGHTS={"=$(vCSAT)"}>} $(vCSAT))

Not sure if the above expression would work

You are getting products with A from $(vCSAT)???

Re: Set analysis to variables

oscar_ortiz — Tue, 10 Oct 2017 18:31:14 GMT

You cannot have nested aggregations without using the AGGR function.

From help:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

Returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a 'local chart', evaluated in the context where the aggr function resides. Each dimension must be a single field. It cannot be an expression (calculated dimension).

If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure. If the expression argument is preceded by the distinct qualifier or if no qualifier is used at all, each combination of dimension values will generate only one return value.

By default, the aggregation function will aggregate over the set of possible records defined by the selection. An alternative set of records can be defined by a Set Analysis expression.

By using this function in Add calculated dimension... it is possible to achieve nested chart aggregation in multiple levels. See also Nested Aggregations and Related Issues.

When used in chart expressions it is possible to achieve Sum of Rows in Pivot Tables.

Examples:

aggr( sum(Sales), Country )

aggr( nodistinct sum(Sales), Country )

aggr( sum(Sales), Country, Region )

count( aggr( sum(Sales), Country ))

Good luck

Oscar

Re: Set analysis to variables

Anonymous — Wed, 11 Oct 2017 07:51:05 GMT

First of all thanks for your time.

Sadly it is still not working :S.

Yes, vCSAT have values in A if that is what you mean.

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 12:33:00 GMT

Can you share some sample data with expected results?

Maybe then we can put something together for you.

Re: Set analysis to variables

Anonymous — Wed, 11 Oct 2017 12:55:12 GMT

Sadly I can not share the data I have as it is from a client of the company where i work. And it comes from a database where i do not have direct access.

Sorry and thanks! If you need it i will explain any doubt about the problem.

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 12:58:14 GMT

But you can mock up some fake data to behave similar to the problem you are having. You would never want to put real data on the community.

Then based upon that data explain what the results should be and we can try to help you with your problem. Right now all I can do is guess.

Thanks

Oscar

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 13:04:46 GMT

So the problem you are having is with your variable. The variable acts as a replacement value. If you copy the contents of your variable and paste it into your expression the expression will fail. The reason it fails is because you cannot do a nested aggregation in an expression without using the AGGR. In order to use the AGGR you need to also define at what dimension(s) you want to choose.

So you may try something like this:

avg({$<Product Line='A'>} Aggr( $(vCSAT), GSS ))

But again this is simply a guess at this point.

Good luck

Oscar

Re: Set analysis to variables

MK9885 — Wed, 11 Oct 2017 13:05:45 GMT

First create a copy of your original

Then load only top 1000 records from each table

Scramble the data in front end thru Document Properties>Scrambling>>>

Upload the qvw here?

Re: Set analysis to variables

Anonymous — Wed, 11 Oct 2017 13:34:55 GMT

I have made an excel with sample data. I give you the excel and the qlik with the table that i can't get working.

The result that i want is:

Showing only the sum of the metric vCSAT for the three product lines (A,B,C). This sum is called GSS.

The TCE_RSLT_METRIC_CSAT_DSAT_TOTAL is only counting how many lines have a value for TCE_RSLT_METRIC_CSAT.

Remember the variable: (it is also in the qlik)

SET vCSAT= sum(TCE_RSLT_METRIC_CSAT_FLAG*if(vWithWeights=1,TCE_RSLT_WEIGHTS,1))/sum(TCE_RSLT_METRIC_CSAT_DSAT_TOTAL_FLAG*if(vWithWeights=1,TCE_RSLT_WEIGHTS,1));

Thank you for your help!

**Note that before i said the average, doesn't matter, with one solved i get the other as you know ^^

Re: Set analysis to variables

Anonymous — Wed, 11 Oct 2017 13:40:58 GMT

This is not working as GSS is not a dimension.

Thanks and sorry for the confusion! Hope that he new data helps!

Re: Set analysis to variables

MK9885 — Wed, 11 Oct 2017 13:59:09 GMT

Not sure if this is correct...

Cus you don't have dimension as ='GSS'

I had to use Product Line as Dimension and added one extra variable

vWidthWeights =1

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 13:59:19 GMT

What should the value of the variable vWithWeights be?

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 14:07:56 GMT

Also, you don't have a column defined: TCE_RSLT_WEIGHTS

What should the values of this field be?

Re: Set analysis to variables

Anonymous — Wed, 11 Oct 2017 14:09:06 GMT

it is 1 or 0 depending on a button that enables or disable the weight. TCE_RSLT_WEIGHTS is the weight (importance) assoscieted to each product line.

Sorry for not especifing before

Thanks

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 14:11:08 GMT

That column doesn't exist in your sample data.

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 14:13:12 GMT

Also, based on the data you provided what should your expected results be?

Re: Set analysis to variables

Anonymous — Wed, 11 Oct 2017 14:17:19 GMT

It is a value that defines the "importance".

Notice that when the variable vWithWeights=0, this whole column is not used, so it is not relevant for the problem. You can just omit it.

Thanks for your time

Re: Set analysis to variables

Anonymous — Wed, 11 Oct 2017 14:21:19 GMT

This should be the result

Re: Set analysis to variables

oscar_ortiz — Wed, 11 Oct 2017 14:25:52 GMT

Is that result based upon the sample data you provided?