Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jamelmessaoud
Contributor

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

Re: Help with expression

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

13 Replies
jamelmessaoud
Contributor

Re: Help with expression

Tried this but didn't work unfortunately

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

MVP
MVP

Re: Help with expression

May be this

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

MVP
MVP

Re: Help with expression

May be this

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

jamelmessaoud
Contributor

Re: Help with expression

This just comes back with zero unfortunately

MVP
MVP

Re: Help with expression

What is your chart dimension? Only Supplier?

jamelmessaoud
Contributor

Re: Help with expression

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?

MVP
MVP

Re: Help with expression

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
Contributor

Re: Help with expression

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

MVP
MVP

Re: Help with expression

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

Community Browser