Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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 ?