Above/Below functions on Top N

[jdecowski]

Hi,

I'm attempting to plot orders in a line graph against orders in the previous year, but only show the most recent 4 years. I'm using the definitions below, but the last data point from the previous year measure drops off (in 2015). Are there any patterns for solving this?

Year = IF( YEAR(ORDER_TIME.autoCalendar.Year) > (YEAR(AGGR(MAX(all ORDER_TIME.autoCalendar.Year ,ORDER_TIME.autoCalendar.Year) ) - 4) , ORDER_TIME.autoCalendar.Year , null )

Orders = COUNT(ORDER_ID)
Orders (previous year) = BELOW( COUNT(ORDER_ID), -1)

Thanks for any help!

[sunny_talwar]

Try this may be

Year

ORDER_TIME.autoCalendar.Year

Orders

If(Year(ORDER_TIME.autoCalendar.Year) > Year(Max(TOTAL ORDER_TIME.autoCalendar.Year)) - 4, Count(ORDER_ID))

Orders (previous year)

If(Year(ORDER_TIME.autoCalendar.Year) > Year(Max(TOTAL ORDER_TIME.autoCalendar.Year)) - 4, Below(Count(ORDER_ID), -1))

You might also be able to use set analysis, but the syntax will have to be tested to get it to work.

[jdecowski · Accepted solution]

This works, but I'm unable to hide the values of the dimension that are null.

If I use Limit Top 4 or the dimension function below, I end up with the same issue I posted on. Any way to easily solve this?

Year = IF( YEAR([REQUEST_TIME.autoCalendar.Year]) > ( YEAR( AGGR( MAX(ALL[REQUEST_TIME.autoCalendar.Year]), [REQUEST_TIME.autoCalendar.Year]) ) - 5) , [REQUEST_TIME.autoCalendar.Year] , Null() ) 

Thanks again!

[sunny_talwar]

Have you unchecked 'Include zero values' under Add-ons -> Data handling