topic Re: equivalent for aggr in QlikView

equivalent for aggr

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

i am using aggr function in an expression but performance is heavily affected. is there any alternate?

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 15:07:07 GMT

eg:

variable vtype = (If([Client Type] = 'Client', '[Client ID]','[Customer ID]'))

in the expression, i am using

Aggr(Maxstring($(vtype )),Country)

Re: equivalent for aggr

swuehl — Wed, 17 May 2017 15:07:11 GMT

Maybe or maybe not. Hard to tell with the information provided.

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 15:08:32 GMT

hi stefan,

above is the example expression. anyway to simplify it?

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 15:11:49 GMT

couldnt able to share data..

but calculating revenue of client or customer for the country...

some country the type is client some country type is customer

this page is for global page design, which involves a chart to show revenue for all countries..(So it is a mix of client and customers)

so based on the country it will pull up the type and calculate revenue.

Gautham

Re: equivalent for aggr

swuehl — Wed, 17 May 2017 15:13:28 GMT

What's the context of your expression? Where are you using it?

How does your data model look like?

The variable is defined with a leading equal sign, so it returns either of the two field names?

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 15:18:04 GMT

variable is just this (If([Client Type] = 'Client', '[Client ID]','[Customer ID]'))

i have to calculate revenue based on Client type of the country,

for some country Client Type is Client then calculate revenue of all client id of that country

for some country Client Type is not client then calculate revenue of all Customer id of that country

Aggr(Maxstring($(vtype )),Country) will give me client id or customer id for country in the dimension.

sum({<Aggr(Maxstring($(vtype )),Country) >} Revenue)

Re: equivalent for aggr

swuehl — Wed, 17 May 2017 15:28:14 GMT

Can't see how this should work. Are you using

Aggr(Maxstring($(vtype )),Country)

as calculated dimension in a chart? And why are you aggregating the clients / customers using Maxstring()?

Your expression

sum({<Aggr(Maxstring($(vtype )),Country) >} Revenue)

does not seem to use correct set analysis syntax.

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 15:37:46 GMT

hi stefan,

attached a small example of how my data looks..

from the example, i want sum of revenue based on Ftype..please help

Gautham

Re: equivalent for aggr

Anil_Babu_Samineni — Wed, 17 May 2017 15:43:27 GMT

What is the expected result from that Straight table?

Re: equivalent for aggr

swuehl — Wed, 17 May 2017 15:47:59 GMT

Maybe like this

country	FType	If( FType = 'Client', sum({<Type = {ClientID}>}sales) , sum({<Type = {CustomerID}>}sales) )
		800
Pakistan	Customer	400
UK	Client	200
USA	Client	100

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 15:51:24 GMT

hi stefan,

i could able to write the if condition, but the problem is there are other business conditons in the expression and performance is bad,

is there a way to simplify this using set analysis

If( FType = 'Client',

sum({<Type = {ClientID}>}sales)

, sum({<Type = {CustomerID}>}sales)

)

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 16:03:15 GMT

i tried something like this sum({<Type = {$(=vFType)}>}sales)...may i know why the variable is not picking client ID or Customer ID based on the country which is used as dimension?

Re: equivalent for aggr

trdandamudi — Wed, 17 May 2017 16:09:19 GMT

Give a try on the below:

sum({<Type = {'$(vFType)'}>}sales)

Re: equivalent for aggr

gauthamchilled — Wed, 17 May 2017 16:16:17 GMT

no luck, syntax error in set modifier e

Re: equivalent for aggr

trdandamudi — Wed, 17 May 2017 16:22:14 GMT

How did you defined your vFType ? Meaning if you had a = while defining the variable then remove the = and it should work.

Re: equivalent for aggr

swuehl — Wed, 17 May 2017 22:09:00 GMT

Maybe consider simplifying your data model. You can easily create a flag field that flag the facts you want to sum.

country	FType	Sum({<FactFlag = {1}>} sales)
		700
Pakistan	Customer	400
UK	Client	200
USA	Client	100