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: 
Not applicable

Selecting max / min values

Hi guys,

I need to extract the max and the min values using the following expression:

sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))-(sum({$<[P&L Category] = {'Cost'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))) / if(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))<0,-1*(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))),((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))))

The expression is used to show profit margin for a product family. How do I find out what is the highest and the lowest margin? I need this to be the expression in the input box as I'll use the number somewhere else.

Thanks.

1 Reply
Nicole-Smith

You probably need something like:

max(aggr(sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))-(sum({$<[P&L Category] = {'Cost'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))) / if(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))<0,-1*(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))),((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))), YourDimensions))

and

min(aggr(sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))-(sum({$<[P&L Category] = {'Cost'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))) / if(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))<0,-1*(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))),((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))), YourDimensions))