Tricky Set Analysis Scenario

[#1]

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.

Thanks in advance for any help you can provide.

[#2]

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.

Cheers!

[namrata_maheshw]

we can achieve  that directly in UI by using below expression-

SUM(AGGR(max(AMOUNT),ACCOUNTNUMBER))