topic Re: Rank for only first - Straight table chart in QlikView

Rank for only first - Straight table chart

kira_whopper — Wed, 27 Jul 2016 12:23:19 GMT

Hi there,

I'm trying to create a column in a straight table for showing the market leader of a certain dimension.

In other words:

Dimension is Molecule, then I have the Molecule Sales (Sum(sales)), then I need the name of the Corporation which Rank(TOTAL <Molecule> Sum(sales)) = 1 and then this Corporation's Sales.

What I achieved so far is to show the market leader in a text object only when one molecule is selected, using this:

Only(

Aggr(

if(

Rank(TOTAL

Sum( IMP_VL_DEMAND_MTH)

) = 1,

Corporation

), Corporation

)

But this does not work in the straight table chart.

I've created an example (attached).

While selecting MolA, the straight table should display CorpH and the sales value of 918,98. This should happen without having to select the molecule.

Any ideas?

Re: Rank for only first - Straight table chart

Clever_Anjos — Wed, 27 Jul 2016 12:35:18 GMT

Try

Market Leader:

FirstSortedValue(Corporation,-Aggr(Sum(Sales),Molecule,Corporation))

Re: Rank for only first - Straight table chart

kira_whopper — Wed, 27 Jul 2016 12:47:35 GMT

Works as a charm! Thanks a lot!

Used this for the market leader sales:
FirstSortedValue(Aggr(Sum(Sales),Molecule,Corporation),-Aggr(Sum(Sales),Molecule,Corporation))

Re: Rank for only first - Straight table chart

Clever_Anjos — Wed, 27 Jul 2016 12:51:43 GMT

>>"Used this for the market leader sales:"

I that case I think that

Max(Aggr(Sum(Sales),Molecule,Corporation)) should perform better

Re: Rank for only first - Straight table chart

kira_whopper — Wed, 27 Jul 2016 13:06:35 GMT

Re: Rank for only first - Straight table chart

arvind1494 — Wed, 24 Jan 2018 12:40:13 GMT

TRY THIS ONE.

FirstSortedValue(Corporation,-Aggr(Sum(Sales),Molecule,Corporation))

Re: Rank for only first - Straight table chart