topic Re: aggr if in set analysis in QlikView

aggr if in set analysis

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

Hi all,

I would appreciate some help with getting the right result for the below expression. I have 3 dimensions Consultant, Fiscal Year, Site.

The two expressions I am trying are: In simple words, I want to sum(Activity) and count(Distinct (AttendanceDate)) with some exclusions

=if(Specialty='Supplies', sum({$<Consultant-={'Mr C Mithun', 'Collins', 'M James', 'R Carl', 'Mr M Smith'}>} Activity),

if(Specialty='Accounts', sum({$<Consultant-={'K Romel', 'Ian Wong', 'Hayden'}>} Activity),

sum({<{'YES/NO'='YES'}>}Activity))

=if(Specialty='Supplies', count({$<Consultant-={'Mr C Mithun', 'Collins', 'M James', 'R Carl', 'Mr M Smith'}>} DISTINCT (AttendanceDate)),

if(Specialty='Accounts', count({$<Consultant-={'K Romel', 'Ian Wong', 'Hayden'}>} DISTINCT (AttendanceDate)),

COUNT({<{'YES/NO'='YES'}>}, DISTINCT(AttendanceDate))

What these does is they remove the values and it gives a 0 value against the names but I want the names to be excluded from the pivot.

I tried aggr if(specialty-.....), Consultant, Fiscal Year, Site) as well but doesn't seem to work. Any help is appreciated.

Thanks,

Karthik

PrashantSangle — Tue, 07 Mar 2017 09:57:26 GMT

Hi,

go to chart Property > presentation > tick supress zero values.

Regards

tunoi — Tue, 07 Mar 2017 11:54:56 GMT

If above proposal dont work then you can try:

sum({$<Consultant-={'Mr C Mithun', 'Collins', 'M James', 'R Carl', 'Mr M Smith'}, Specialty={'Supplies'}>} Activity)

sum({$<Consultant-={'K Romel', 'Ian Wong', 'Hayden'}, Specialty={'Accounts'}>} Activity)

sum({<{'YES/NO'='YES'}, Specialty-={'Accounts', 'Supplies'}>}Activity))

and the same modification for Count

infock12 — Tue, 07 Mar 2017 12:08:25 GMT

Thanks Prashant, that worked.

infock12 — Tue, 07 Mar 2017 12:11:43 GMT

Thanks Ionut, very helpful.