Selecting max / min values

Tue, 14 Jan 2014 15:23:39 GMT

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.

Re: Selecting max / min values

Nicole-Smith — Tue, 14 Jan 2014 16:22:14 GMT

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

topic Selecting max / min values in QlikView

Selecting max / min values

Re: Selecting max / min values