A few columns of my data set include Year, Month, AccountNumber, and Amount. Often an AccountNumber will show up for multiple Months and/or Years, but I only want to count each AccountNumber once each year.
I created one bar graph with "Year" as my X-axis and "Number of Accounts" as my Y-axis to show a year-over-year comparison of managed accounts. I used the following expression to give me the output I want:
Count({<Year=,Month=>} DISTINCT AccountNumber)
The Set Analysis for Year and Month are needed because those filters are set to "Always one selected value". For this graph, I want to ignore those 2 filters yet allow all others.
The problem I'm having now is how to create this same chart but using "Amount" as my Y-axis. I can't use a similar expression since the AccountNumber is the identifier I need, yet instead of counting those I want to sum the corresponding Amounts. I hope this makes sense.
I actually ended up using a 3rd solution as Gysbert's wasn't working properly (the data in the chart was changing with the month/year selection which I need to avoid) and yours populated a 1 in every "flag" cell so that didn't work either.
I created a case statement in the SQL code I was using to create my data set that creates a new column, only pulling in the amount once per account.
My example wasn't very good, so unfortunately the solutions provided didn't work when I applied them to my actual file.
Thanks for all of the help though as these different view points did get me to the solution, even it was in a round-about way.
