topic top 10% PERCENT by date in QlikView

top 10% PERCENT by date

Gaëtan — Fri, 16 Nov 2018 11:24:20 GMT

Hello,

I want to select the TOP 10% of my entreprises which generate the most revenue.

Today i use this expression :

=if(aggr(Rank(Sum(CA)),DATE_DAY,ENTREPRISE_ID)<=round(count(TOTAL ENTREPRISE_ID)*0.10),Sum(CA))

it work fine if i'm at entreprise_id level but I want a vision by day for do a graph and have a evolution by day

Do you have a solution ?

Thanks in advance

Re: top 10% PERCENT by date

jyothish8807 — Fri, 16 Nov 2018 11:35:20 GMT

May be try like this:

=if(aggr(Rank(Sum(CA)),DATE_DAY)<=round(count(TOTAL ENTREPRISE_ID)*0.10),Sum(CA))

Re: top 10% PERCENT by date

Gaëtan — Fri, 16 Nov 2018 11:49:06 GMT

Thanks for the reply,

I already test this expression and It's not work . it give me the total of the revenue (1476)

Re: top 10% PERCENT by date

jyothish8807 — Fri, 16 Nov 2018 11:56:11 GMT

Could you please share your expected output, i am not still clear with your requirement.

If you want the list at Date level, then the output will be one value for one date.

Re: top 10% PERCENT by date

Gaëtan — Fri, 16 Nov 2018 12:08:41 GMT

hello,

for exemple

20 entreprise ID : 10% = 2 entreprise_iD

I must select the entreprise 7 and 18 because they have the most revenue in the list : total revenue 180

I want only the result of this calcul for each day

I want tell for every day : 10% of my top entreprise generat X % of my revenue

thanks

Re: top 10% PERCENT by date

sunny_talwar — Fri, 16 Nov 2018 12:38:36 GMT

May be something like this

=Aggr(If(Rank(Sum(CA)) <= Round(Count(DISTINCT TOTAL <DATE_DAY> ENTREPRISE_ID) * 0.10), Sum(CA)), DATE_DAY, ENTREPRISE_ID)

Re: top 10% PERCENT by date

Gaëtan — Fri, 16 Nov 2018 13:16:50 GMT

same problem, here my application

Re: top 10% PERCENT by date

sunny_talwar — Fri, 16 Nov 2018 13:28:05 GMT

You only have a single dimension (DATE_DAY) in your CA Graph and the expression outputs 2 value... what do you want to do with them? Sum them? Avg Them?.. for Sum you can try this

=Sum(Aggr(If(Rank(Sum(CA)) <= Round(Count(DISTINCT TOTAL <DATE_DAY> ENTREPRISE_ID) * 0.10), Sum(CA)), DATE_DAY, ENTREPRISE_ID))

For Avg

=Avg(Aggr(If(Rank(Sum(CA)) <= Round(Count(DISTINCT TOTAL <DATE_DAY> ENTREPRISE_ID) * 0.10), Sum(CA)), DATE_DAY, ENTREPRISE_ID))

Re: top 10% PERCENT by date

Gaëtan — Fri, 16 Nov 2018 13:54:26 GMT

Thank you !!! it works perfect

indeed, I forget to add a sum / avg