topic Bar chart drill down issue in QlikView

Bar chart drill down issue

Wed, 06 Oct 2010 12:28:39 GMT

Hi,

I am using bar chart to show the ranks depends on the sum(values)

using rank(sum(sales)) in an expression ,chart show the ranks of a different product(soap,deodrant,cosmetics,,,,).correctly at first shot,.

when i click the particular product,,the rank changed as 1

for exp: if i hv product 'Deodrant' with rank 4 in bar chart,,,when i click on this particular product it drills down and showing the rank as 1..Its continuous for all product when i made click on it particular.

i need it to be displayed as 5 after it drills down as a single product.Pls help ,,,.thanks...

Bar chart drill down issue

Wed, 06 Oct 2010 13:32:24 GMT

Hi,

try a formula like this :
max(Aggr(Rank(Sum({$<product=>} Sales),4),product))

I'm not sure that the most efficient way to do it but it should work

Regards

Bar chart drill down issue

Thu, 07 Oct 2010 05:34:49 GMT

Hi Bertrand,

max(Aggr(Rank(Sum({$<product=>} Sales),4),product)) its working..

can you explain me in detail each funcion how....pls it will be helpful for me.

Bar chart drill down issue

Thu, 07 Oct 2010 09:34:53 GMT

Hi,

Sum(Sales) : returns the sum of sales

Sum({$<product=>} Sales (or Sum({<product=>} Sales) : returns the sum of sales disregarding current selections on product field (please refer to the "set analysis" part in the QlikView Reference Manual).

rank(Sum({$<product=>} Sales),4) is just the rank function applied on the expression detailled above. (please refers to QlikView Help on "rank" function if necessary), the "4" parameter is just a choice
about how do you want the rank function to manage numbers it's not necessary anyway.
That expression will give you the correct result but will always display every member of the field product (because we are disregarding selections
on products using a set analysis expression).

Aggr(Rank(Sum({$<product=>} Sales),4),product)
I'm using the Aggr function : Well, we can consider it as a sub-request : the aggr is first executed then the result is used as usual in a bar chart.
That the most simple way I found to calculate the rank disregarding the product selections (if you don't do this, the rank will always be 1 when you select one product) but to also taking the product selection into account for the bar chart (if you don't do this, the bar chart always displays all products even if you select only one). please refer to QlikView help on the Aggr function to have complete details about it.

And last, the max function, well, is completely useless :). I just wrote it too quickly

Hope it helps