Change year with an input box

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

Thanks 🙂