How do I show both a numerical display and a trend on the same report?

Andrew Clauson shared this question 6 months ago
Answered

I'm trying to design a set of KPI numerical displays (each at a point in time) with a 12 month trend line underneath. The first attached screenshot shows what I'm after ("Total Assets" on the far left).

Setting up the trend lines has been straightforward. The Data section of the Report Builder is shown in the second attached screenshot - it's a simple crosstab.

The point-in-time value I want to show for each KPI is simply the last row in the crosstab - the value at the most recent date. However, I can't figure out how to retrieve just that last result for a numerical display.

Is this even possible? If not, if I built the point-in-time numerical displays as a separate report, is it possible to overlay that report on top of the trend line report? Or is there a better approach I should consider?

Comments (5)

photo
1

Hi Andrew,


It sounds like you might be able to use an Append Subquery to get the result you want, where you can filter the entire Append Subquery with the granularity of your data.


How this might look is that if your Time Series data is measured every month, you can use the Predefined Date Filter for 'Between' the 'Last Calendar Month', and then drag in your several metrics a second time, where the only value that should populate is the most recent one. You can then use this in the Numeric Display Chart to overlay on your canvassed report. Find images attached.


Let me know if this helps.


Kind regards,

Simon

photo
1

Hi Simon,

Thanks for your response. It sounds like that would accomplish what I’m after.

However, I think I’d need to filter the subquery based on the most maximum date in the data (not sure if that’s possible) rather than the last calendar month per the system, as it could be different depending on the dataset selected.

Anyway, will give it a shot and let you know if it works.

Thanks again,

Andrew

From: Support Queue <support@Yellowfin.bi>

Reply-To: Support Queue <support@Yellowfin.bi>

Date: Sunday, March 22, 2020 at 5:12 PM

To: Andrew Clauson <andrew@profinancial.bc.ca>

Subject: New Comment in "How do I show both a numerical display and a trend on the same report?"

photo
1

Hi Simon,

Yep, that seems to work. I need to play around with my filter and view logic to make it do exactly what I’m after, but it’s working well as is.

Thanks again for your help. I think we can close this ticket as resolved.

Andrew

From: Support Queue <support@Yellowfin.bi>

Reply-To: Support Queue <support@Yellowfin.bi>

Date: Sunday, March 22, 2020 at 5:12 PM

To: Andrew Clauson <andrew@profinancial.bc.ca>

Subject: New Comment in "How do I show both a numerical display and a trend on the same report?"

photo
1

Hi Andrew,


That's not a problem, I'm glad I could point you in the right direction!


If it's the MAX date value in that row, you might be able to use a Calculated Field: MAX(date_value), or you could use an Advanced Function that shows you the Top N(1) values from the date column.


Let me know if any of these suggestions work for you, or if you require further assistance.


Kind regards,

Simon

photo
1

Hi Andrew,


I'm just letting you know that I'm marking this question as answered.


If you require further assistance, please don't hesitate to respond to this message and I will get back to you shortly.


Kind regards,

Simon