topic Problem with Aggr() (example file included) in QlikView

Problem with Aggr() (example file included)

Wed, 11 Jan 2012 23:26:19 GMT

Hi, I am having some weird behavior using the Aggr() function, as fas as I know, it should execute an expression based on a dimension, so

aggr(

expr1

,Dim1

)

expr1 will be executed the number of times that Dim1 exist, but for some weird reason, it does not work as expected on the attached file, please download it and see how can I achieve the expected result.

Basically the scenario is the following:

I have some data loaded in an inline table:

As you see its the same ContactID, same Hour, but sometimes different Quarter.

I am adding the following set analysis since I want to group it by 2 dimensions inside a pivot chart:

sum(
     aggr(
           if(
                Sum({$<IsInQueue={1}>}ContactDurationQH) < num(ServiceLevelAgreement*1000)
           ,1
           ,10
           )
     ,ContactID
     )
)

Basically this is asking:

"If the sum of ContactDurationQH flagged as IsInqueue for the current subset of data is less than the ServiceLevelAgreement by 1000, output a 1, if not, a 10"

But is is not working as expected:

As a matter of fact, it is returning a 10 in the first row, which is wrong, since the sum is less than SLA * 1000, and in the second, it's not even returning valid data from the IF

Any idea of what is going on?

Thanks in advance for the time.

Regards

-ed

Problem with Aggr() (example file included)

Thu, 12 Jan 2012 04:34:06 GMT

I didn't need to use the aggregate function to produce the results you requested. The following script returns a value of "1" in the Test column when fully expanded and returns a value of "10" when collapsed. Please let me know if this solves your problem.

if (Sum({$<IsInQueue={1}>}ContactDurationQH) < num(ServiceLevelAgreement*1000),1,10)

Problem with Aggr() (example file included)

Thu, 12 Jan 2012 15:46:53 GMT

Thanks a lot Scott, I think I forgot to mention that in my source tabe there will be tons of Contacts sharing the same hours and quarters, that's why I used Aggr.

-ed

Problem with Aggr() (example file included)

Fri, 13 Jan 2012 15:04:15 GMT

Does any one has any idea why this exmple cannot work using AGGR() ?

Suggestions?

Regards

-ed

Problem with Aggr() (example file included)

Fri, 13 Jan 2012 17:29:28 GMT

Finally I got a solution from QV Support, its using dimensionality():

if(Dimensionality()=2,
     aggr(
           if(
                Sum({$<IsInQueue={1}>}ContactDurationQH) < num(ServiceLevelAgreement*1000)
           ,1
           ,10
           )
     ,Quarter),

     if(Dimensionality()=1,
     aggr(
           if(
                Sum({$<IsInQueue={1}>}ContactDurationQH) < num(ServiceLevelAgreement*1000)
           ,1
           ,10
           )
           ,Hour))
     )

But I have been using Aggr() on many other expressions in cunjuction with Pivot charts, this is the first time that I'll have to include this new function "Dimensionality", have any of you played around with it?

I just want to double check if this is the best approch towards solving my issue.

Regards,

-ed