יום רביעי 01 אוגוסט 2012 04:40
My data schema is very simple. Each week a store puts in their name, the week number, the year, and their total sales. We use this data in a number of reports / spreadsheets and it has served us well for a few years now.
But today someone asked for a new report and I'm stumped on how to build it. I filter the data for the year I want (2012). Then each column is the week #. Each row is the store name and their total sales, ranked highest to lowest for that week. Ideally there would be a guide along the left counting 1-total number of stores so you could quickly see "Ahh, store 17 was 5th in sales for week 15"
When I try to put the Week as a Column Group I get the message "If one or more column groups is specified, all fields in the Values field list must have an aggregate function specified."
The closest I've come is putting the week in the row groups and then I can expand each week to see who was tops that week. But that isn't ideal, especially for printing.
And I haven't figured out a way at all to do the list of numbers (1-whatever number of stores there are) for the ranking.
Thanks for your help on this, sorry I'm so clueless :(
יום רביעי 01 אוגוסט 2012 05:09
I remembered running into this before and how I figured it out --
I created a table with the Week column and the Sum of Sales for the data.
I created another table that had the store name and the sum of sales in the values.
I went back to the first table and deleted the Sum of Sales. I then cut and paste the second table into the value box and created a nested table and this gave me exactly what I wanted -- each week, 1- whatever.
I eventually trimmed down the nested table to a single cell, a single expression
=RowNumber("StoreandSales") & " - " & Fields!StoreName.Value & " - " & FormatCurrency(Sum(Fields!SaleAmount.Value),0)
Where "StoresandSales" was the name of the nested Table and the FormatCurrency got rid of the cents that aren't needed for this report.
- סומן כתשובה על-ידי Sean P T יום רביעי 01 אוגוסט 2012 05:09
יום שני 06 אוגוסט 2012 11:01מנחה דיון
Glad to hear that you have got the issue resolved. Thanks very much for your sharing. That's very helpful.
TechNet Community Support