topic Re: Bottom 5 sales in QlikView

Bottom 5 sales

Fri, 09 Jan 2015 07:51:35 GMT

Hello ,

i am using this expression to find comparison between Actual Margin and Quoted Margin sorted for 5 highest revenue giving account using this expression

If(Aggr(Rank(SUM(Revenue),4),Account)<=5,Account), however i am trying to calculate same thing for bottom 5 revenue ,

please suggest

Re: Bottom 5 sales

Anonymous — Fri, 09 Jan 2015 08:25:50 GMT

Hi,

Try:

One way to derive bottom 5 records,the aggregate value need to multiplied by -1.

=aggr(If(RANK(aggr(-1 * sum(Revenue), Account)) <= 5, Account), Account )

If(Aggr(Rank(-SUM(Revenue),4),Account)<=5,Account)

=aggr(if(rank(-sum(Revenue))<=5,Account),Account)

Regards

Neetha

Re: Bottom 5 sales

Mon, 12 Jan 2015 10:26:04 GMT

Hi, Neetha

i tried both expression but its not working in my case , for you i have attached sample excel file and a qvw file, kindly review and suggest.

Re: Bottom 5 sales

jzimolong — Mon, 12 Jan 2015 10:46:33 GMT

I can't open the .qvw, can you provide me with the dimensions and expressions? Looking at your question, have you experimented with dimension limits, using the "Show Only" smallest 5?

Re: Bottom 5 sales

nikhilgarg — Mon, 12 Jan 2015 11:08:40 GMT

HEy,

I think if you have 20 records then you want last five records. Right ??

In that case you should use following expr:

If(Aggr(Rank(SUM(Revenue),4),Account)>=15,Account)`

I think it should work

Re: Bottom 5 sales

Mon, 12 Jan 2015 11:15:14 GMT

I am using Calculated Dimension "If(Aggr(Rank(-SUM(Revenue),4),AccountName)>5,AccountName)" and using expression to calculate actual margin and quoted margin

(SUM(Revenue) - SUM(Cost))/SUM(Revenue) -for actual margin

(SUM(QuotedPrice) - SUM(QuotedCost)) / (SUM(QuotedPrice))-for quoted margin

i am trying to show comparison between actual margin and quoted margin of five bottom most revenue generating project. if i use dimension limits it will restrict accordingly depending on first expression which is expression calculate actual margin.

Re: Bottom 5 sales

Mon, 12 Jan 2015 11:26:31 GMT

I am using Calculated Dimension "If(Aggr(Rank(-SUM(Revenue),4),AccountName)>5,AccountName)" and using expression to calculate actual margin and quoted margin

(SUM(Revenue) - SUM(Cost))/SUM(Revenue) -for actual margin

(SUM(QuotedPrice) - SUM(QuotedCost)) / (SUM(QuotedPrice))-for quoted margin

the expression that you suggested will give all 15 records

Re: Bottom 5 sales

nikhilgarg — Mon, 12 Jan 2015 11:37:19 GMT

Are you sure that my following expression will give all 15 records ?

If(Aggr(Rank(SUM(Revenue),4),Account)>=15,Account)

Because it will give only records having ranks either equal to greater than 15.

thanks

Re: Bottom 5 sales

kavita25 — Mon, 12 Jan 2015 12:24:13 GMT

Hi,

According to your excel sheet Bottom 5 Account Name should be :

B	$1,880.00
A	$1,500.00
D	$1,200.00
C	$930.00
C	$358.30

Right??

Regards,

Kavita

Re: Bottom 5 sales

Anonymous — Mon, 12 Jan 2015 12:47:20 GMT

Hi Prashant,

Check attached files.

Please check is it as per your requirement

Regards

Neetha

Re: Bottom 5 sales

Anonymous — Tue, 13 Jan 2015 08:11:17 GMT

Hi Prashant,

try:

=If(Aggr(Rank(-SUM([Total Invoiced Revenue]),4),AccountName)<=5,AccountName)

Re: Bottom 5 sales

nikhilgarg — Tue, 13 Jan 2015 08:17:42 GMT

Hey neetha,

Cann't we do like this that:

Select AccountName as Dimension

Put below expression :

Sum({<AccountName = {"=Rank(Sum([Total Invoiced Revenue]))< 5"}>}[Total Invoiced Revenue])

is this approach right ??

Re: Bottom 5 sales

Anonymous — Tue, 13 Jan 2015 08:27:13 GMT

Depending on requirement we can use this

Re: Bottom 5 sales

nikhilgarg — Tue, 13 Jan 2015 08:32:28 GMT

Ohk ,

but actually it is not giving the correct result

Re: Bottom 5 sales

Anonymous — Tue, 13 Jan 2015 08:36:47 GMT

Its grouping the Total Invoiced Revenue data and giving results for account name

Re: Bottom 5 sales

nikhilgarg — Tue, 13 Jan 2015 09:38:19 GMT

Ohk,

Then how to get the last 5 account names based on Total Invoiced Revenue data ??