topic Dimension Limit, declining products in QlikView

Dimension Limit, declining products

Mon, 20 Feb 2012 07:01:29 GMT

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!

Re: Dimension Limit, declining products

CELAMBARASAN — Mon, 20 Feb 2012 07:20:35 GMT

Hi,

Use the below expression for rank

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

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

Dimension Limit, declining products

tresB — Mon, 20 Feb 2012 07:21:43 GMT

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

Dimension Limit, declining products

Mon, 20 Feb 2012 07:30:26 GMT

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

Dimension Limit, declining products

jagan — Mon, 20 Feb 2012 07:30:36 GMT

Hi,

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

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

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.

Dimension Limit, declining products

jagan — Mon, 20 Feb 2012 07:32:43 GMT

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.

Dimension Limit, declining products

Mon, 20 Feb 2012 07:36:01 GMT

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

Dimension Limit, declining products

jagan — Mon, 20 Feb 2012 07:45:50 GMT

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.

Dimension Limit, declining products

jagan — Mon, 20 Feb 2012 07:46:14 GMT

Hi,

Can you attach the sample file.

Regards,

Jagan.

Re: Dimension Limit, declining products

Mon, 20 Feb 2012 07:58:49 GMT

Sir,

attached is a sample file of my problem.

Thank you!

Re: Dimension Limit, declining products

jagan — Mon, 20 Feb 2012 08:16:34 GMT

Hi,

Please check the attachment.

Regards,

Jagan.

Re: Dimension Limit, declining products

qliksus — Mon, 20 Feb 2012 12:24:35 GMT

Hi ,

Please find the attached file with rank included

Dimension Limit, declining products

jagan — Mon, 20 Feb 2012 12:31:34 GMT

Hi,

Superb, Good use of Accumulation here.

Regards,

Jagan.