Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

Hi All

My Table 1 display all customer. it last column display sales contribution % for all customer.

My Table 2 display only TOP 3 customer. it last column display sales contribution % for all customer.

May i know how to make my Table 2 sales contribution compute only for Top 3. so that the % contribution as below :-

=[COMPANY] Sales        Sales % Cum

                      89,344,812 100%

NOBI PUTRA ANGKASA, PT 34,364,010 38%

MUHAMAD YASIN 27,769,691 31%

MANDALA MITRA KARYA, PT 27,211,111 30%

My current expression is below :-

Sum(sales)/Sum(TOTAL(sales))

it should be :-

Sum(sales)/Sum(TOTAL(rank<4(sales))

But it cannot work , Hope some can advise me.

Paul

1 Solution

Accepted Solutions

Re: Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

This?

Capture.PNG

Expressions

money(Sum({<$(ColumnDim101) = {"=Rank(Sum(sales)) < 4"}>}sales),$(vMoneyFormatK))

Sum({<$(ColumnDim101) = {"=Rank(Sum(sales)) < 4"}>}sales)/Sum(TOTAL {<$(ColumnDim101) = {"=Rank(Sum(sales)) < 4"}>}sales)

6 Replies
paulyeo11
Valued Contributor II

Re: Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

my qvw

MindaugasBacius
Valued Contributor II

Re: Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

Use Dimension Limits in Chart properties:

Screenshot_1.jpg

paulyeo11
Valued Contributor II

Re: Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

Hi Mind

Thank you for your reply. ( I am using ver 9 )

I can limit the top 3 customer ,

Now i present in Table , i need to know how to  change the expression .

below only compute Top 3.

Sum(sales)/Sum(TOTAL(sales))


Paul

MindaugasBacius
Valued Contributor II

Re: Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

The first expression:

If(Aggr ( Rank ( Sum(sales)/Sum(TOTAL(sales))) , Customer ) <= 5, money(Sum(sales)/$(Columndim89),$(vMoneyFormatK)))

The second one:

If(Aggr ( Rank ( Sum(sales)/Sum(TOTAL(sales))) , Customer ) <= 5, Sum(sales)/Sum(TOTAL(sales)))

Re: Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

This?

Capture.PNG

Expressions

money(Sum({<$(ColumnDim101) = {"=Rank(Sum(sales)) < 4"}>}sales),$(vMoneyFormatK))

Sum({<$(ColumnDim101) = {"=Rank(Sum(sales)) < 4"}>}sales)/Sum(TOTAL {<$(ColumnDim101) = {"=Rank(Sum(sales)) < 4"}>}sales)

paulyeo11
Valued Contributor II

Re: Sum(sales)/Sum(TOTAL(sales)) compute all Total customer sales , How to make it compute only Top 3 customer Total ?

Hi Sunny

Your expression very powerful , Thank you very much it work fine.

Paul

Community Browser