topic Re: set analysis (aggr) in QlikView

set analysis (aggr)

adiarnon — Sun, 05 Jun 2016 12:53:37 GMT

hi i have this table-

project	unit	actual	budget
A	aa	9	0
A	bb	5	4
B	aa	5	2
C	bb	2	6
C	ff	4	0
C	cc	2	4
D	bb	0	0
F	aa	2	9

i need an taxt box that will count the number of projects that have at least one unit that have actual and dont have budget

in our case 2 projects - A,C

How should i write the expression?

adi

Re: set analysis (aggr)

karthickv — Sun, 05 Jun 2016 13:18:15 GMT

Hi Adi,

For your example with the below expression:

sum( aggr(if(sum(actuals)>0 and sum(budget)=0,1,0),Project,Unit))

Incase if there are more than one Unit with budget as zero and you still want to count only projects try to use the below:

sum(aggr( if(sum(aggr(if(sum(actuals)>0 and sum(budget)=0,1,0),Project,Unit) ))>0,1,0),Project))

Regards,

Karthick.

Re: set analysis (aggr)

sunny_talwar — Sun, 05 Jun 2016 14:15:46 GMT

Try this script:

Table:

LOAD project,

unit,

actual,

budget,

AutoNumber(project&unit) as Key

FROM

[https://community.qlik.com/thread/219558]

(html, codepage is 1252, embedded labels, table is @1);

Text box expression:

=Count(DISTINCT {<Key = {"=Sum(actual) > 0 and Sum(budget) = 0"}>} project)

Re: set analysis (aggr)

sunny_talwar — Sun, 05 Jun 2016 14:17:56 GMT

Without script manipulations, you can try this:

=Count(DISTINCT Aggr(If(Sum(actual) > 0 and Sum(budget) = 0, project), project, unit))

Re: set analysis (aggr)

adiarnon — Sun, 19 Jun 2016 07:28:37 GMT

its working!

but the calculation taking a very long time

do you have any idea how can i do it more efficient? ( quickly)

adi

Re: set analysis (aggr)

antoniotiman — Sun, 19 Jun 2016 09:07:42 GMT

Hi Adi,

have You tried without Aggr(), simply

Count({<actual-={0},budget={0}>} DISTINCT project)

Regards,

Antonio

Re: set analysis (aggr)

sunny_talwar — Sun, 19 Jun 2016 12:03:54 GMT

I did provide a quicker solution where you would have to create a Key field in the script. You should see performance benefits if you are able to and are willing to do it.

Best,

Sunny