Hello! On a Dashboard showing contribution history, I am trying to order Campaign Fiscal Year in descending order. I cannot find a filter that does this... am I missing it? Looking at the SQL it shows:{ "explicit": true, "multiSelection": true, "members": [ "2023", "2022" ]}
I tried inserting "order by: DESC" after the closing bracket, but it says it's invalid. Any ideas? Am I way overthinking this? Thanks in advance,
Thanks for the feedback, however there is no option for that for these filters.
The a to z (ascending or descending) is not on the filters. Click the pencil (edit) button on a widget. It is on the part where you add the rows/columns/values (on the left side of a widget not the right side where the filters are).
The filters do not have this option.
Right, I did not mean the "Filters" on the right, I meant the filter options on the values you add to widgets. Working with the Campaign Fiscal Year, under columns, there is no simple way to get the years to list in Descending order. I have sorted with A-Z before on other ones, so that's not an issue. Have you done a widget like this one, to show multiple years in descending order? Thanks again for responding,
If it doesn't allow you to sort by the columns, you could use the Filtered Values concept. Instead of adding the Campaign Fiscal Year to the columns part of the pivot table, you filter your values and then you can sort them by moving them around. Look at the picture below. I was able to display the column part as I wanted by the order I placed them in the Values part. The syntax for filtered values is ([Value], [Filter1], [Filter2], [FilterN]). In other words, ([Total Amount], [Campaign Fiscal Year]). With the campaign fiscal year filtered for the year you want.
Does that make sense?
Yes! Definitely. I thought about this option, but was hoping there was another way. I'll just give this one a go!Thank you for taking time to correspond with me, Neil! Happy weekend when we get there!
I hear you there. I'd prefer the easy button option as well.
One last thought, I noticed your pivot has Campaign goal amount. In case you get inflated values there, the concept you are looking for is a Multi-pass Aggregation Formula.
An example is Budget Amounts. Budget Amounts for Performances, Campaigns, Funds, etc. will show up multiple times and will inflate your totals.
Syntax:
SUM ( [Campaign ID], MAX([Budget Amount]) )
The above formula just says, Group the data by Campaign ID, take the MAX budget amount per Campaign ID, and then SUM the Budget Amounts.
The above formula can show as SUM ( [Campaign ID], ([Max Budget Amount]) ) which is the same as SUM ( [Campaign ID], MAX([Budget Amount]) ).