Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
There is a chart showing monthly price dynamics for two years (current & previous).
There are also two straight lines showing avg price of current year and previous year.
On the same sheet user can set filters by month and year, but these filters do not affect the chart. It always shows full max year from the selection + the previous one (i.e. 2019 + 2018, 2018 + 2017, etc).
All other selections should be applied to the chart. I.e., if user needs data of specific product or customer.
Is it possible to show Avg CY line only for this year, and Avg PY line only for prev.year? (see example below)
I tried If function "if(Year = max(Year), then)", but it makes Year & month selections applied:
If March is selected, it shows single point for March;
If only 2019 year is chosen, there is nothing shown for 2018.
Any advice on the topic is appreciated.
Thank you!
use total keyword in your avg expression
like avg(total expressiondefinition)
did you try
year=max({1}year) ?
Thank you for your answers. I got some progress using max({1}Year). Now the graph is perfect if 2019 year is chosen and there is no selection by month.
There are two problems now:
1) If 2018 is chosen, PY line disappears;
2) If there is a selection by month, PY line is shown for both years, and CY line is shown only for selected months
Current expressions are the following:
Avg CY:
If(Calendar.Year = max({1}Calendar.Year),
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year))'} > }Sales)
/
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year))'} > }Volume)
)
Avg PY:
If(Calendar.Year <> max({1}Calendar.Year),
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year)-1)'} > }Sales)
/
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year)-1)'} > }Volume)
)
hello,
in your set analysis, try to add
Calendar.Month=
which will inhibit all selections made on month