set analysis (aggr)

[adiarnon]

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

[karthickv]

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.

[sunny_talwar]

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)

[sunny_talwar · Accepted solution]

Without script manipulations, you can try this:

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

[adiarnon]

its working!

but the calculation taking a very long time

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

adi

[antoniotiman]

Hi Adi,

have You tried without Aggr(), simply

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

Regards,

Antonio

[sunny_talwar]

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