aggr if in set analysis

[infock12]

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 · Accepted solution]

Hi,

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

Regards

[tunoi]

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]

Thanks Prashant, that worked.

[infock12]

Thanks Ionut, very helpful.