Dimension Limit, declining products

[#1]

Hi,

I want to have a chart that shows the declining products (negative %YAGO) and rank those products based on the current year sales. just like the screenshot: how can I do this in qlikview? please do help me.. Thanks a lot!

[CELAMBARASAN]

Hi,

     Use the below expression for rank

     =Rank(-[FY2011]/[FY2010])

     Or

     Use Rank(-[% YAGO])

     Do you mean rank based on the Current year sales the use sort by expression as

     =Rank(Sum({<Year={$(=Max(Year))}>} Sales))

Hope it helps

Celambarasan

[tresB]

May be you can use RANK function on your sales field and then sort the table on ranked field.

Have i understood right?

Regards,  tresesco

[#4]

But how can I show only those products that have negative %YAGO?

[jagan]

Hi,

Use the Rank() to calculate rank for an expression.

Rank = Rank((Col(3) - Col(2))/Col(2))

OR

Rank = Rank(([FY2011] - [FY2010])/[FY2011])

If your expressions are dynamic then use first expression or else use the second one.

Hope this helps you.

Regards,

Jagan.

[jagan]

Hi,

Use the If condition in all the expressions

If([FY2011] - [FY2010] < 0, Your expression, 0)

If a row has all 0s then qlikview automatically removes those.

Regards,

Jagan.

[#7]

so you mean i will use this as a calculated dimension: If([FY2011] - [FY2010] < 0, Your expression, 0)?

[jagan]

Hi,

This is not a calculated dimension

FY2011 - is your 2011 year expression

FY2010 - is your 2010 Year expression

In all the expressions which you have in your chart, first check it is negative value, if it negative then display as such or else just return 0 in If statement else condition.

If([FY2011Expression] - [FY2010Expression] < 0, Expression, 0)

Hope this helps you.

Regards,

Jagan.

[jagan]

Hi,

Can you attach the sample file. 

Regards,

Jagan.

[#10]

Sir,

attached is a sample file of my problem.

Thank you!