topic Re: having clause in set analysis? in App Development

having clause in set analysis?

ali_hijazi — Mon, 26 Apr 2021 11:06:47 GMT

Hello got a pivot table with 2 dimensions:
Consultant and Project Type
the expression is counting the man-days
what I actually want is display consultants who got more than 1 man-day per project type
check this example:

Tarrieux Olivier should appear with "Customer Annual Account" and Extension only
so what I'm trying to accomplish is give me the count of man-days by consultant and project type having count of man-days greater than one
kindly advise

Re: having clause in set analysis?

GaryGiles — Mon, 26 Apr 2021 11:40:43 GMT

For the dimension expression for Consultant, you could use:

=if(aggr(count(distinct [Instance Company]),Consultant,[Project Type])>1,Consultant)

And uncheck the "Include null values" options for the dimension.

Re: having clause in set analysis?

ali_hijazi — Mon, 26 Apr 2021 11:43:54 GMT

Hello Gary
I don't want to include this condition in the expression of the dimension because I have other expressions to add
isn't there a way in set analysis?
I try to avoid if else as much as possible

Re: having clause in set analysis?

GaryGiles — Mon, 26 Apr 2021 12:31:55 GMT

I agree with you on trying to avoid the if statement, but I'm not sure how else to accomplish this when you are aggregating on 2 fields (Consultant and [Project Type].

If you create a conbination field in the load script of Consultant&[Project Type] called Consultant_ProjectType, you could use something like:

=count({$<Consultant_ProjectType={"=Count(Distinct [Instance Company])>1"}>} Distinct [Instance Company])

This is possible when aggregating on one field, but I'm not sure how to use this technique over 2 fields.

Re: having clause in set analysis?

ghaliabed — Tue, 27 Apr 2021 04:23:51 GMT

I tried placing the above in a count:

Count

( Distinct

if(aggr(count(distinct [Instance Company]),Consultant,[Project Type])>1,Consultant)

)

It seems to be working with different fields also

Anything to consider with this ?