topic Re: Rank Sum issue in QlikView

Rank Sum issue

Anonymous — Tue, 29 Jul 2014 11:38:41 GMT

Hello ,

The expression below works fine when the "Amount" field is a positive number looking for the solution for the reverse when it's negative.Tried to negate the sum function but it not working.

sum({<Name = {"=rank(sum(Amount))<=$(Top)"}>} Amount)

where $(Top) is an input box variable

Re: Rank Sum issue

MK_QSL — Tue, 29 Jul 2014 11:41:00 GMT

What you want when the amount is negative values? May be

sum({<Name = {"=rank(sum(If(Amount<0,-Amount,Amount)))<=$(Top)"}>} Amount)

sum({<Name = {"=rank(sum(If(Amount<0,-Amount,Amount)))<=$(Top)"}>} IF(Amount<0,-Amount,Amount))

Re: Rank Sum issue

Anonymous — Tue, 29 Jul 2014 11:58:40 GMT

Those didn't work either.

Sorry! Let me add some more clarity to my question. This amount is always negative in this instance, and so when I input a number in the input box say 5 the expression I required is to calculate the top amount by the Name(dimension).The expression works fine with the sales fieldname which is always a positive.

Thanks.

Re: Rank Sum issue

MK_QSL — Tue, 29 Jul 2014 12:01:33 GMT

Provide sample data in excel file along with required output... Thanks

Re: Rank Sum issue

Anonymous — Tue, 29 Jul 2014 12:30:51 GMT

Hi couldn't attach the file kindly make use of the screen shot.Thanks

Re: Rank Sum issue

MK_QSL — Tue, 29 Jul 2014 12:58:10 GMT

Create a Straight Table

Dimension

Name

Expression

SUM({<Name = {"=Rank(SUM(Amount),4)<=5"}>}Amount)

SUM({<Name = {"=Rank(SUM(Amount),4)<=$(vTop)"}>}Amount)

Re: Rank Sum issue

Anonymous — Tue, 29 Jul 2014 13:21:22 GMT

That didnt work either.I have already created already a straight table and used the dimension limits to make it work. I show only the smallest and the value is the variable $(Top) which a dynamic for user input.

Now I want to replicate the this in an expression and display in a Text box.

Re: Rank Sum issue

MK_QSL — Tue, 29 Jul 2014 13:26:39 GMT

SUM({<Name = {"=Rank(SUM(Amount),4)<=$(vTop)"}>}Amount)

Re: Rank Sum issue

Anonymous — Tue, 29 Jul 2014 14:53:20 GMT

@Manish Kachia

Quite unfortunate it's not working

Re: Rank Sum issue

MK_QSL — Tue, 29 Jul 2014 15:29:24 GMT

What you want to show in List BOX? Total of $(vTOP) ?

Re: Rank Sum issue

Anonymous — Tue, 29 Jul 2014 15:49:13 GMT

Exactly! The total amounts of the $(vTop).

Re: Re: Rank Sum issue

MK_QSL — Tue, 29 Jul 2014 15:59:09 GMT

Check enclosed file

Re: Re: Rank Sum issue

Anonymous — Tue, 29 Jul 2014 16:18:22 GMT

Appreciated.Curious to know what the "Number 4 " does.

Thanks a million once again

Re: Rank Sum issue

MK_QSL — Tue, 29 Jul 2014 16:22:44 GMT

rank([ total ] expression [ , mode [, format ] ])

Evaluates expression, compares the result with the result of the other rows containing the current column segment and returns the ranking of the current row within the segment. For bitmap charts, the current column segment is defined as it appears in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure).

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order.

The ranking is returned as a dual value, which in the case when each row has a unique ranking will be an integer between 1 and the number of rows in the current column segment.

In the case where several rows share the same ranking, the text and number representation can be controlled as follows:

The second parameter mode specifies the number representation of the function result.

mode

0 (default)	If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1	Lowest rank on all rows.
2	Average rank on all rows.
3	Highest rank on all rows.
4	Lowest rank on first row, then incremented by one for each row.