Hi!
Here is my problem. I have a chart that is supposed to display the top 5 performing cities.
To correctly rank my report by top Cities i had to employ the use of the rank() function. Those outside the top 5 are thrown into a catch all bucket 'Other'.
When i run the tabular report in the query itself, the results look fine, however when I try to graph it out in a bar chart, the results are way off. In fact depending on what i do, I often get the results to look identical (usually the grand total of ALL cities) across all the bars. It gets worse when i try to stack another metric in a stacked bar chart.
Here is logic behind my [City, ST] field that goes on the x-axis. I have both Aggregate and Rollup set to None.
x-axis: if( rank(total([Month Spend Amount] for [City, ST])) >5) then ('OTHER') else (upper([City, ST]))
Any suggestions? Many thanks!