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: 
jamelmessaoud
Creator II
Creator II

Help with expression

Hi guys,

This should be simple but I'm struggling to write the correct expression!

I have two measures.

  • Sum([Suppliers FTL Cost])
  • Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate])

I want to do a count of a field where the first measure is greater than the 2nd measure (and vice versa). 

Count Suppliers where Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]) is basically what i want to achieve

Can anyone help?

Thanks

J

1 Solution

Accepted Solutions
sunny_talwar

Try it without DISTINCT before Supplier

Sum(Aggr(If(Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), 1, 0), Supplier))

View solution in original post

13 Replies
jamelmessaoud
Creator II
Creator II
Author

Tried this but didn't work unfortunately

count(if(Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), Supplier))

sunny_talwar

May be this

Sum(Aggr(If(Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), 1, 0), YourDimensions))

sunny_talwar

May be this

Sum(Aggr(If(Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), 1, 0), Supplier))

jamelmessaoud
Creator II
Creator II
Author

This just comes back with zero unfortunately

sunny_talwar

What is your chart dimension? Only Supplier?

jamelmessaoud
Creator II
Creator II
Author

I tried this Sunny

sum(Aggr(If(Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), 1, 0), distinct Supplier))

The Supplier is a name field if that helps?

sunny_talwar

Try it without DISTINCT before Supplier

Sum(Aggr(If(Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), 1, 0), Supplier))

jamelmessaoud
Creator II
Creator II
Author

What if i wanted to do the distinct though?  That not possible?

sunny_talwar

Have you tried it yet? Is it not giving you distinct?