Decreasing Goal Line

[crystles]

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.

[oleg_orlov]

Can you do it in a load script?

[crystles]

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

[RedSky001 · Accepted solution]

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)   )

[crystles]

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.

[RedSky001]

The expression does that (see the attached document)

[crystles]

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?

[RedSky001]

Can you upload an example .qvw file

[crystles]

Here is an example with some sample data.

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

[RedSky001]

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