Get previous year value

[#1]

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?

[#2]

Hi,

You can try this:

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

Hope this helps!

Best,

Maya

[#3]

Thanks, Maya

I remake you code to:

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

but it's not working, unfortunately.

[johnw · Accepted solution]

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.

[#5]

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

[#6]

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.