How can I use specific fields for X and Y axes in report chart in salesforce without using any aggregated functions?

Question

I want following 2 fields of my custom object to be present on the report chart.

1. Tier (Bronze/Silver/Gold/Platinum)
2. Tier Date (Indicates the date when the customer is upgraded or downgraded to particular tier).

So, my goal is to prepare a chart that shows when the Custom Tier upgraded/downgraded over the time. For that, Tier Date should be on X axis and Tier should be on Y axis. But I'm not allowed to use both fields without using aggregated functions like sum, count, avg etc. in salesforce report builder.

Here I'm attaching screenshot of expected output.

Can anyone have idea how can I archive this chart?

Answer 1

So, you have to be aggregating something when it comes to report charts, at least for the line graph you're looking at there. For the tiering you describe, I'd suggest putting together a row-level formula.

Something like the following formula should work (though you might need to tweak the variable name to match):

CASE((Tier__c), "Bronze",1, "Silver",2, "Gold",3,"Platinum",4,0)

That said, though, bear in mind that history is not necessarily saved. To get change over time recorded properly you'll need "Historical Trending" (or another custom object to store the timestamp and status fields, but that way takes longer), with the "Tier" field selected as one to track:

Otherwise, you'll just get "whatever the tier currently is, plus whenever you last changed it for them".