topic Re: Above/Below functions on Top N in App Development

Above/Below functions on Top N

jdecowski — Sat, 16 Nov 2024 05:51:31 GMT

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!

Re: Above/Below functions on Top N

sunny_talwar — Mon, 13 May 2019 14:35:03 GMT

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.

Re: Above/Below functions on Top N

jdecowski — Mon, 13 May 2019 17:04:55 GMT

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!

Re: Above/Below functions on Top N

sunny_talwar — Mon, 13 May 2019 17:48:29 GMT

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