Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeJones
Creator
Creator

Exclude a row from a dimension though include in the value in the Total

Below shows my prefered outcome

Current Table Planned Jobs   Desired Table Planned Jobs
Total 400   Total 400
Employer 1 100   Employer 1 100
Employer 2 50   Employer 2 50
Direct 250      

 

To get my desired table I have used for the dimension =if(Employer<>'Direct',Employer) and the box to include null values is empty.

Measure uses If(Dimensionality() = 0, Sum({<JobType={'XX'}>} Jobs),
Sum({<JobType={'XX'}, Employer-={'Direct'}>} Jobs))

Using the above the total shows as 150.  When include null vales, the total is correct though I get a dash in the Employer with a value of 0.

How can I get the Desired Table to show in Qlik

 

Labels (4)
1 Solution

Accepted Solutions
EmmaMcAlister
Luminary
Luminary

Hi Mike,

Try changing your dimension from =if(Employer<>'Direct', Employer)  to Employer
and your measure to IF(ROWNO() = 0, Sum({$<JobType={'XX'}>} Jobs),
Sum({$<JobType={'XX'}, Employer-={'Direct'}>} Jobs)).

Then you should be able to set a limitation on your Employer field to only show rows where your value is >0.

EmmaMcAlister_1-1736339356690.png

 

 


Let me know if it works!

View solution in original post

4 Replies
marcus_sommer

What happens if you just used Employee as a native dimension - with your shown expression branching the calculation in regard to the object-dimensionality? 

MikeJones
Creator
Creator
Author

Thanks for response Marcus.  What do you mean use Employer  as a native dimension?

I don't understand what you are meaning with regard to o the object-dimensionality.

EmmaMcAlister
Luminary
Luminary

Hi Mike,

Try changing your dimension from =if(Employer<>'Direct', Employer)  to Employer
and your measure to IF(ROWNO() = 0, Sum({$<JobType={'XX'}>} Jobs),
Sum({$<JobType={'XX'}, Employer-={'Direct'}>} Jobs)).

Then you should be able to set a limitation on your Employer field to only show rows where your value is >0.

EmmaMcAlister_1-1736339356690.png

 

 


Let me know if it works!

MikeJones
Creator
Creator
Author

Hi Emma, thanks for your help, it works wonderfully.