10 Replies Latest reply: Jun 12, 2013 11:46 AM by ajdavis1 RSS

    Tricky Set Analysis Scenario

      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.