Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Sept. 16, Qlik Product Portfolio Strategy and Roadmap for Data Integration 11 AM ET REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner
Partner

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
Partner
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
Partner

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 ?