I am completely confused by something that should be extremely simple. Which is worrisome, because this project is going to get a lot less simple very soon.
All I'm doing, at this early stage in my project, is trying to make sure some target data I'm working with rolls up properly. My source is set up very simply, as a flat file with three column only: location (12 locations), date (all dates are the last day of the quarter, with 12 quarters represented), and target (integer). In other words, each of 12 locations has one target for each of 12 dates.
Right now, I can get all the numbers to show up properly in a table when I put YEAR and QUARTER on the columns shelf, location on the rows shelf, and drop MAX(Target) on text in the marks box. Or, I can get them to display properly in a column chart by putting area, YEAR, and QUARTER on the columns chart and MAX(Target) on the rows chart.
Now, it makes sense to me that I should be able to pick any other aggregation for total since there's only one value for each area-date combination....and it's almost true. I can choose MAX, MIN, AVERAGE, or MEDIAN and get the correct (only) number, but as soon as I change it to SUM all **** breaks loose. The numbers blow up like crazy and i can't tell why. I can't even figure out how to reproduce the numbers--but there's clearly some multiplication involved, as most of the "SUM"s are far larger than the actual sum of all my data points.
As an example, if I choose MAX(Target) for area 1, I get the CORRECT value of 8,071 for 2019 Q2. If I change to SUM(target) I get 60,101,276 (!!!). If I manually sum all the areas' 2019 Q2 targets, I get the correct total of 162,500. But If I switch to SUM(Target) and remove area, I get 2,530,334,993. What is happening here?
This is a big problem because I need to be able to remove area from the view to show the sum total for each time frame. I will also need to bring in an additional "office" tier of aggregation. (When I do that I will remove the current target numbers and replace them with numbers that sum up to the area level).
What am I doing wrong here? This seems like it should be one of the easiest things to do and i feel sure that I've done this is a previous project, in tutorials, etc. I can't tell what's different here.
I have attached an excel spreadsheet with the source.
targets.xlsx 16.6 KB