topic Re: Aggr() and IF in QlikView

Aggr() and IF

fcecconi — Tue, 30 May 2017 19:00:56 GMT

Is it possible to use an IF statement in the DIM?

I want to something like this

Avg(Aggr(Days old, if UserIsActive=1, UserName, 'X-Employee')

I want to get a average of Days Old by UserName, but only if they are active. If they are no loner active, I want to lump the days old together and then use it in the average.

thanks

Re: Aggr() and IF

Oleg_Troyansky — Tue, 30 May 2017 19:13:51 GMT

Hi Frank,

Yes, you can use an IF statement in a calculated dimension, and you are almost there with your syntax. Before I can offer a specific solution, I need you to describe your requirement a bit more:

- what do you want to see in your Dimension values? Names of active Users? Or, the Average Number of Days, like in a histogram?

- What do you want to see in your Expression(s) ? Avg value of Days old? Or, Number of Users hat have the same avg number of Days old? Or anything else?

With that information in hand, we can determine together if you need an AGGR() or perhaps you need something else...

You can learn more about AGGR(), Set Analysis and many other advanced techniques at the Masters Summit for Qlik - coming soon to Boston, MA. If you can't attend our session, I can suggest reading my book QlikView Your Business, where I also describe these advanced QlikView techniques.

cheers,

Oleg Troyansky

Re: Aggr() and IF

fcecconi — Tue, 30 May 2017 19:21:35 GMT

Hi Oleg

Actually I am looking for an overall average. So basically I want to lump the superiors into 1 group, x employees into another group and keep active by themselves. I want to then get the overall average.

=Avg(Aggr(Avg({<Case.Status={'Open Case'},SFUser.UserRoleName={'Care Advocate', 'Care Advocate Supervisor'}>}Case.Age_in_Days),

if(SFUser.UserRoleName='Care Advocate Supervisor', 'Supervisor',

IF(SFUser.IsActive=0, 'X Employees',

IF(SFUser.UserId >0,

if(len(SFUser.Name)>0, SFUser.Name,'X Employees'),

SFUser.Name)))))

Re: Aggr() and IF

Oleg_Troyansky — Tue, 30 May 2017 19:46:13 GMT

Well, it doesn't quite work like this - the function AGGR() can allow aggregating data by fields, not by calculated dimensions. (You could use IF in a Calculated CHART Dimensions, but not in an AGGR dimension).

In order to implement this logic, I'd recommend adding a field like "Employee Type", verify these IF conditions in the data load script, and then use this new field as your AGGR() dimension.

cheers,

Oleg Troyansky

Re: Aggr() and IF

fcecconi — Tue, 30 May 2017 20:13:55 GMT

Oleg

thanks! I added the logic to the load and is providing the correct results