Year-on-Year comparison using above function

[thomasgrimsby]

Hi,

I have two charts, the line chart at the top is just a sum(Value) chart but the stacked bar chart at the bottom is a sum(Value) - above(Sum(Value)) chart.

The reason for using the above function is to do a year-on-year comparison, explaining the reason behind the top chart.

When you take a look at the time dimension along the x-axis you can see that the top chart reflects the Years selected from 2007 - 2018.

Do you know how I can make that happen for the chart at the bottom which for now starts from 2008 - 2018?

I know that the reason why the chart at the bottom starts from 2008 is that the first bar is showing the 2008 minus 2007 value.

But do anyone know how to use the above function and also make it react to the selected years.

Is there a Time - 1 I can add into my expression?

[sunny_talwar]

What are the two dimensions in the bottom chart? May be try this

Sum(Value) - Aggr(Above(Sum(Value)), Dimension2, Dimension1)

[thomasgrimsby]

Hi Sunny!

I have two dimensions; a cyclic group on Time (yearly, quarterly, monthly, weekly) and Product so the expression on the chart at the bottom is:

avg(

     aggr(

          avg({<TD_QueryName={'NWERESID'}>}TD_Value)

               ,Product

               ,$(=getcurrentfield(Time))

     )

)

-

avg(

     aggr(

          above(

               avg({<TD_QueryName={'NWERESID'}>}TD_Value))

               ,Product

               ,$(=getcurrentfield(Time))

     )

)

The expression is tested and working fine but I would like to be able to display all selected Years.

Including Year 2007 up to 2018 in my example snapshot.

[sunny_talwar]

What does the above doing? not showing all the selected years?

[thomasgrimsby]

The above function is taking the selected Year minus the Year before.

The chart is here displayed in a table:

"This period" is just the sum(Value) and the "Last period" is the sum(Value) - above(Sum(Value)).

[sunny_talwar]

I understand all that... but you are saying that your tried expression isn't showing all the years? What years is it showing and which one it isn't? Also, can you try this

Aggr(Avg({<TD_QueryName={'NWERESID'}>}TD_Value) - Above(Avg({<TD_QueryName={'NWERESID'}>}TD_Value)), Product, $(=GetCurrentField(Time)))

[sunny_talwar]

Or try this

Aggr(Avg({<TD_QueryName={'NWERESID'}>}TD_Value) - Above(Avg({<TD_QueryName={'NWERESID'}, Year, Date, Month, MonthYear, Quarter>}TD_Value)), Product, $(=GetCurrentField(Time)))

[thomasgrimsby]

The first chart is showing all my selected years but the second chart is not showing the first selected year.

The starting point for the two chart is different, 2007 for the top one and 2008 for the bottom one.

The example snapshot I sent is showing an active selection on year 2007 - 2018.

I tried to include the time dimension to the set analysis but it is still showing me 2008 as starting point.

Just wondering if there is a workaround to get the two charts to have the same starting Time.

[sunny_talwar · Accepted solution]

Instead of just adding the time dimension to set analysis, add all those date and time dimensions into set analysis where you might make a selection... for example, you have been saying that you made a selection in year... did you ignore selection in the expression within above()... because as soon as your select 2008.... the value for 2007 is out of selection and above() can only bring 0 down because of the value not present... but ignore selection in year... you can now bring it down...

Does this make sense?