Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

having clause in set analysis?

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:

ali_hijazi_0-1619435072357.jpeg

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

I can walk on water when it freezes
4 Replies
GaryGiles
Specialist
Specialist

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.

 

ali_hijazi
Partner - Master II
Partner - Master II
Author

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 can walk on water when it freezes
GaryGiles
Specialist
Specialist

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.

 

ghaliabed
Partner - Creator
Partner - Creator

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 ?