Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Part of the revenue coming from the top 1% of the customers (top1% in volume)

Hi,

This is the data:

FiscalYearBusinessCustomerIncome

1213

A

A

1$

1213

AB2$
1314AA4$
1314AB3$
1415AA5$
1415A could be B....B6$

I would like in a straight table with:

Formula121313141415
=count(distinct Customer)Number of customer300350400
=round(count(distinct Customer)/100)Top 1% of customer in volume344
=sum({<Customer={"=Aggr(Rank(Sum({<Business={'A'}>}Income),1,1),Group)<= TOP1% "},Business={'A'}>}Income)/sum({<Business={'A'}>}Income)Part of the total revenue coming from the top 1% customers (i.e Revenue with rank max = count( distinct customer)/100)

My issue is to take the top 1%. I made a variable like this:     vRankmax: round(count(distinct Customer)/100)   and used   $(vRankmax) in the formula, but it's not working. Can you help me please? Thanks.

Best regards,

Ludo

5 Replies
Not applicable

Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

Little mistake: in the table it's

=sum({<Customer={"=Aggr(Rank(Sum({<Business={'A'}>}Income),1,1),Customer)<= TOP1% "},Business={'A'}>}Income)/sum({<Business={'A'}>}Income)

Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

Would be able to share a sample to take a look at the issue?

MVP
MVP

Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

I would start with something like this (assuming vRankMax is defined with a leading equal sign and evaluated to a number):

=Sum(

      Aggr(

          If( Rank( Sum(Income)) <= vRankMax, Sum(Income)),

          Customer, Business, FiscalYear

          )

)

Adapt the aggr() dimensions to the required granularity for the rank.

Then you should be able to use above expression e.g. in a chart with dimensions Business and FiscalYear.

If you want to use a set expression, you would need to create and define keys with the required granularity for the field modifier with the ranking search,Customer alone is probably not granular enough.

Not applicable

Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

Hi Swuehl,

This doesn't work because the only dimension in the straight table has to be: Fiscal Year only.

Then, I have multiples straight tables in which I will manually modify the dimension: business.

The result consist in a kind of map of all the different business, represented by one straight table for each.

-------

In your formula, how do I look at the results to one particular business for example?

Best regards,


Ludo

MVP
MVP

Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

Maybe like this for business A

=Sum({<Business={'A'}>}

      Aggr(

          If( Rank( Sum({<Business={'A'}>} Income)) <= vRankMax, Sum({<Business={'A'}>}Income)),

          Customer, Business, FiscalYear

          )

)

or maybe

=Sum({<Business={'A'}>}

      Aggr(

          If( Rank( Sum({<Business={'A'}>} Income)) <= vRankMax, Sum({<Business={'A'}>}Income)),

          Customer, FiscalYear

          )

)

If your chart dimensions are less granular than the aggr() dimensions, the results of the aggr() function will be projected to the chart dimensions, using the outer aggregation function ( Sum() ), i.e. all Customer Income above threshold per Businees A and per fiscal year will be grouped by fiscal year and summed.