Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Thanks 🙂