topic Re: Set Analysis Question in QlikView

Set Analysis Question

aaronnayan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

Really quick

i have a KPI :

max(aggr($(vMargin),[CustomerName]))

Which gives the value £

I want to get the Name 'String'of the customer with the most Margin?

Thanks

Re: Set Analysis Question

OmarBenSalem — Mon, 22 Jan 2018 14:43:09 GMT

maybe:

=firstsortedvalue ([CustomerName], -aggr( max($(vMargin)),[CustomerName]))

=firstsortedvalue ([CustomerName], -max(aggr($(vMargin),[CustomerName])))

Re: Set Analysis Question

OmarBenSalem — Mon, 22 Jan 2018 14:44:59 GMT

per analogy :

='Customer with the most Sales : '&firstsortedvalue (customer, -aggr( sum(sales),customer))

result :

Re: Set Analysis Question

aaronnayan — Mon, 22 Jan 2018 14:45:28 GMT

Hi Omar its coming back with a null value

Re: Set Analysis Question

aaronnayan — Mon, 22 Jan 2018 14:48:16 GMT

Both are not working. Maybe becuase of the variable?

Re: Set Analysis Question

OmarBenSalem — Mon, 22 Jan 2018 14:49:02 GMT

what's in the variable?

Re: Set Analysis Question

aaronnayan — Mon, 22 Jan 2018 15:10:02 GMT

I have changed the name of the fields for security

sum({<SInvoiceCount = {1}>} [Marginlcc]) + sum({<SCreditC = {1}>} [Marginlcc])

Re: Set Analysis Question

andrei_delta — Mon, 22 Jan 2018 15:19:26 GMT

hi,

you have a mistake in the formula:

sum({<SInvoiceCount = {1}>} [Marginlcc]]) + sum({<SCreditC = {1}>} [Marginlcc])

hope it helps,

Andrew

Re: Set Analysis Question

aaronnayan — Mon, 22 Jan 2018 15:22:38 GMT

No andrew thanks for spotting that i changed it due to security

The variable works fine on its own

Re: Set Analysis Question

OmarBenSalem — Mon, 22 Jan 2018 15:37:10 GMT

What if u put the expression in ur variable directly in mine?

It works?

Re: Set Analysis Question

aaronnayan — Mon, 22 Jan 2018 15:39:21 GMT

i can confirm this was the result

No Max was needed!!!

=firstsortedvalue([Customer Name], -aggr($(vMargin),[Customer Name]))

Re: Set Analysis Question

vishsaggi — Mon, 22 Jan 2018 15:42:14 GMT

Try this?

= Firstsortedvalue([CustomerName], -aggr(RangeSum(sum({<SInvoiceCount = {1}>} [Marginlcc]), sum({<SCreditC = {1}>} [Marginlcc])), [CustomerName]))

Also check when you use this variable in a textbox are you seeing any value?

Once you confirm you are seeing values. We can implement using variable. Let us know.

With variable try like

= Firstsortedvalue([CustomerName], -aggr($(vMargin), [CustomerName]))