topic Re: Decreasing Goal Line in QlikView

Decreasing Goal Line

crystles — Thu, 23 May 2013 13:34:20 GMT

I have a set of data that I would like to create a decreasing goal line for. It is set up by weekly amounts.

I would like to have it start with a value such as 1.7 and then decrease it by 9.8% for each week.

My formula in excel was simple. [1.7-(1.7*.0098)] in the first row and then in the next row below that, have it reference the cell above it's value and subratact it by 9.8%

[REF - (REF * .0098) and copy that forumula down the excel document.

However, I cannot seem to replicate this formula in Qlikview. I tried using the AGGR function with no success. Does anyone know of a way to complete this formula in Qlikview?

Any help would be greatly appreciated.

Re: Decreasing Goal Line

oleg_orlov — Thu, 23 May 2013 13:45:26 GMT

Can you do it in a load script?

Re: Decreasing Goal Line

crystles — Thu, 23 May 2013 13:56:23 GMT

That could be possible. But I'm not sure how to go about doing it that way either?

Re: Decreasing Goal Line

RedSky001 — Thu, 23 May 2013 13:57:19 GMT

You could use an exprssion similar to this if you want to do it in the UI.

if(RowNo()=1,1.7-(1.7*0.0098), above(Column(1)) - (above(Column(1))* 0.0098) )

Re: Decreasing Goal Line

crystles — Thu, 23 May 2013 15:55:45 GMT

That gave me a very volatile line. I just need a steadily decreasing line.

The values should follow this pattern for the formula, decreasing by 9.8% starting with a value of 1.7

1.7000

1.6833

1.6668

1.6505

1.6343

1.6183

and so on.

Re: Decreasing Goal Line

RedSky001 — Thu, 23 May 2013 16:01:39 GMT

The expression does that (see the attached document)

Re: Decreasing Goal Line

crystles — Thu, 23 May 2013 16:13:10 GMT

It does for a chart, but I need it to represent it in a line graph. I tried it in my application and it gave me a straight line. Even in the table formate it just gave me the value 1.6833 for each row.

I'm not sure what was different except that I have different variables listed in my dimensions (Fiscal Weeks).

I want to plot this Goal line over a set of values. Would that be conflicting with it?

Re: Decreasing Goal Line

RedSky001 — Thu, 23 May 2013 16:26:12 GMT

Can you upload an example .qvw file

Re: Decreasing Goal Line

crystles — Thu, 23 May 2013 16:55:28 GMT

Here is an example with some sample data.

Even with this data it is still showing a straight line for the goal?

Re: Decreasing Goal Line

RedSky001 — Thu, 23 May 2013 20:04:10 GMT

You only need one dimenion (Month) you can remove RowNo()

Re: Decreasing Goal Line

crystles — Thu, 23 May 2013 21:10:06 GMT

Yes I tried that first but then it just gives me a line dependent on the values, I just need a slowing downward sloping line. Is that possible?

Re: Decreasing Goal Line

RedSky001 — Thu, 23 May 2013 21:39:58 GMT

When I downloaded your qvw and just left the month dimension I had a downwards sloping line.

I didn't want to upload the file again being so large it would have taken forever. I'm not around tomorrow so if you have anymore problems I suggest starting a new thread.

Re: Decreasing Goal Line

crystles — Tue, 28 May 2013 11:34:35 GMT

OK I'll look into it further. Thank you for your help!

Re: Decreasing Goal Line

crystles — Wed, 29 May 2013 17:52:51 GMT

This formual does work. I had to switch the order of the expressions. I put this one first and my value expression after it and now it works! Thanks for your help!