Rank values based on dimension not included in chart/table

[#1]

Hi,

Basically would need to get top N but slightly a different tricky requirement with different level of details.

Dummy Data:

Customer     Product     Sales

C1               P1               50

C1               P2               100

C2               P1               110

C2               P2                30

C3               P1                10

C3               P2                 20

Requirement: Top 2 customers (at Product level ) to be presented in a Barchart that contains ONLY Customer and Sales...

Manual Working:

Customer     Product     Sales

C2               P1               110

C1               P2               100

C1               P1               50

...so on

Now Bar chart which contains only customer and Sales object should show C2 (110) and C1(100) values alone..by applying dimensional limits to customer largest 2.

But issue is when we take only customer and sales, sales gets aggregated to customer and we would get rather C1(150) and C2(140)..

If I include Product then Bar chart shows something which is not required...because what is required is only C2 (110) and C1(100)., where 110 and 100 refers to value at product level but product not included in chart.

Is there any way to do this..Kindly suggest...

Thanks,

Qlikshare

[#2]

You can try a workaround without using sum function. Please see attached

[#3]

Hi,

Sorry I could not see the attachment. Could you kindly help to put screenshot or sample code here.

Many Thanks.

Regards,

Qlikshare

[settu_periasamy]

Hi,

You can try with Aggregation function.

Dimension :  Customer

Expression : Aggr(Max(Sales),Customer)

Dimension Limits :  Show Only -> First 2

[#5]

Hi,

Many thanks.It worked. However in actual requirement it is further complicated, got SALES1 and SALES2, need to take difference between SALES1-SALES2 and get the top 2 based on this(SALES1-SALES2) and PLOT IT's RELEVANT SALES1 and SALES2 in GRAPH..

Example data:

Customer    Product    Sales1    Sales2    Difference

C1              P1              50            10          40  

C1              P2              100          88          12

C2              P1              110          100        10

C2              P2                30          10          20

C3              P1                10          5            5

C3              P2                20          17          3

Sort above table Descending by Difference:

Customer    Product    Sales1    Sales2    Difference

C1              P1              50            10          40

C2              P2                30          10          20

C1              P2              100          88          12

C2              P1              110          100        10

...so on..

Now with Aggr(Max(Difference),Customer) and dimension limits set to 2, able to spot out first 2 records.

In Graph:

For Sales1: Need 50 (based on biggest difference), but it takes 110 because of formula Aggr(Max(Sales1),Customer)

For Sales2: Need 10(based on biggest difference),but it takes 100 because of formula Aggr(Max(Sales2),Customer)

Any suggestions please, to resolve this formula for SALES1 and SALES2.

Many thanks.

Regards,

Qlikshare

[jagan]

Hi,

Try like this

Dimension: Product, Customer

Expression: =Aggr(If(Rank(Sum(Sales), 4,0) <=2, Sum(Sales)), Product, Customer)

Hope this helps you.

Regards,

Jagan.

[#7]

Hi,

Sorry, couldn't get the expected result.

Thanks,

Qlikshare

[#8]

Hi,

Any other suggestions to get this Sales1 and Sales2 figures as per in above example.

Many Thanks.

Regards,

Qlikshare

[jagan]

Hi,

Use this expressions

For Sales1:

If(Rank(Sum(Sales1 - Sales2), 4,0)<=2, Sum(Sales1))

For Sales2:

If(Rank(Sum(Sales1 - Sales2), 4,0)<=2, Sum(Sales2))

Regards,

Jagan.