topic Get previous year value in QlikView

Get previous year value

Mon, 21 Dec 2009 22:43:03 GMT

Hi all,

apparently I use wrong syntax -

there's some aggregation "avg(Price)", I need to get this value for previous year and I try to use this expression -

"avg({<Year = $(Year - 1)>} Price)" and result is null ... what's wrong?

Get previous year value

Mon, 21 Dec 2009 22:59:03 GMT

Hi,

You can try this:

avg({<Year={$(#=Only(Year)-1)} Price)

Hope this helps!

Best,

Maya

Get previous year value

Mon, 21 Dec 2009 23:16:19 GMT

Thanks, Maya

I remake you code to:


=avg( { < Year = { $(#=Only(Year)-1) } > } Price )

but it's not working, unfortunately.

Get previous year value

johnw — Tue, 22 Dec 2009 08:37:41 GMT

I suspect you have a deeper problem than syntax (the syntax looks good to me). Are you trying to create a chart like this?

Year Average Price Previous Year's Average Price
2009 50 40
2008 40 45
2007 45 30

If so, then the fundamental limitation is that a set is only built once for the entire chart, not once per row. So the only(Year) portion will actually return null since there are three years for the whole table. It will not return the year for the specific row of the table. I don't believe you can use set analysis alone to build this chart.

Attached is one way of handling a chart like the above. There are certainly other ways. It all depends on the specifics of the situation.

Get previous year value

Tue, 22 Dec 2009 10:23:03 GMT

Hi Dmitry,

Here is another possible solution. You can use an expression similar to what you wrote to create a column in a table for prior period average. In order to do that, you should select the current year from a select list. This solution will always give you the current and prior year values and can be used to creat columns for any number of years in the past. Here are the two columns I use for current and prior:

For the current year use this expression - avg({$<FinYear={$(=Only(FinYear))}>}Sales)

For the prior year use this expression - avg({$<FinYear={$(=Only(FinYear)-1)}>}Sales)

I have attached the solution.

Greg

Get previous year value

Tue, 22 Dec 2009 16:12:33 GMT

John, great thanks for answer. Really, you're rigth - I had tried to use set analysis in wrong way. Unfortunately, I haven't enough time to study QV in details, and I have to get expirience at real tasks.