Change year with an input box

Thu, 02 Feb 2012 22:23:39 GMT

Hi all

I have a problem. I´m trying to make a line chart with year and producut group as a dimensions.

I have two fields: [Average invoiced Price] and [OrderQuantityPerYear)

Year in the bottom and product group showed in the chart.

I would like to have an "input box" where I could change the year for the quantaty but I would like to see the average price per year.

eg, the raw data:

Year Price Quantaty

2009 8€ 100

2010 10€ 120

2011 12€ 150

What I would like to do is.

in my input box I would write 2010, then would like to see

2009 8€ *120

2010 10€ *120

2011 12€ * 120

If I write 2009 then I would like to see

2009 8€ *100

2010 10€ *100

2011 12€ * 100

I have tried to write the expression like this

=avg({Year=>=[Average invoiced Price]) * sum({$<Year={$test)>} OrderQuantityPerYear)

and "test" is the displayed variables but it gives me only zero for the year I haven't write in: eg. 2010

2009 8€ *0

2010 10€ *120

2011 12€ * 0

Please help me!

Thanks

Markus

Re: Change year with an input box

Miguel_Angel_Baeyens — Fri, 03 Feb 2012 03:09:10 GMT

Hi Markus,

You can do that, but you need to aggregate the sum of quantity for that year, for example

Avg(Price) * Aggr(Sum({1< Year = {$(vYear)} >} TOTAL Quantity), Year)

Check the file attached based on your example data.

Hope that helps.

Miguel

Fri, 03 Feb 2012 07:28:19 GMT

Thanks 🙂