Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

crystles
Contributor III

Decreasing Goal Line

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.

1 Solution

Accepted Solutions
msheraton
Contributor III

Re: Decreasing Goal Line

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

13 Replies
oleg_orlov
Contributor

Re: Decreasing Goal Line

Can you do it in a load script?

crystles
Contributor III

Re: Decreasing Goal Line

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

msheraton
Contributor III

Re: Decreasing Goal Line

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
Contributor III

Re: Decreasing Goal Line

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.

msheraton
Contributor III

Re: Decreasing Goal Line

The expression does that (see the attached document)

crystles
Contributor III

Re: Decreasing Goal Line

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?

msheraton
Contributor III

Re: Decreasing Goal Line

Can you upload an example .qvw file

crystles
Contributor III

Re: Decreasing Goal Line

Here is an example with some sample data.

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

msheraton
Contributor III

Re: Decreasing Goal Line

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

Community Browser