Top N by gift amount with fiscal year filter

[danielrsnyder]

I have created a report to look at our fundriasing by fiscal quarter over the past 5 years and also a table to show the top 5 gifts. I wanted users to be able to filter a few different ways so I added the filters in the upper right corner. The fiscal quarter and purpose filters work fine (independently and together), but when you filter on a full fiscal year, gifts are missing from the Top 5 table? Any ideas why that might be happening?

[alexei7]

Hi,

That does sound odd. It sounds like something to do with the way the report is filtering

Is your Fiscal Year column part of the same table as Fiscal Quarter (maybe you have a date table with all these fields in)?

If it is, is that Fiscal Year value next to every date in your date table?

Alex

[danielrsnyder]

Yes, my Fiscal Year and Quarter are in the same date table and there is a FY value next to all dates, just double checked.

[alexei7]

Hmm, that's odd. 

It's quite difficult to tell without being to play about with your model, but I presume that's confidential.

Do all Fiscal Years selected return no results?

Is the join between Gift Date and between the Date in your Date table?

Alex

[danielrsnyder]

I removed confidential information and you should be able to view the file here.

[alexei7]

Thanks, really helpful!

When i'm filtering on Fiscal Year, it's definitely bringing back results - what is missing/what are you expecting to see that's different?

Alex

[danielrsnyder]

Sorry for the confusion, it always gave me results, but not the top 5. For example, if you click on FY Q2 in 2018, there is a gift of $402K in the top 5. But if you filter on the full fiscal year of 2018, that gift (while in the top 5), is skipped over.

[alexei7 · Accepted solution]

Hi,

I think the issue is in your filter for "Gift Amount".

You have Top N --> Top 5 --> By value "Sum of Gift Amount".

You want to change this to "Average of Gift Amount".

It looks to me as if the various $50,000 amount are being totalled and are greater than the $402k

Hope that helps

Alex

[danielrsnyder]

Ah ha! Thanks @alexei7. That seems to be the trick.