My Director of Development needs our overall attendance by state for fiscal year 2023. The problem is we don't have our Geo_Area system table set up yet to pull our zip codes by state (yet, I'm hoping to get that fixed at the TLCC conference next week).
Does anyone else know of a way I might pull what she needs relatively easily without that?
Do your addresses not have a state component?
They do, but our problem is we don't do many online sales (working on increasing that for FY24). So the only way we get data is from asking for zip codes during walk-up sales.
Analytics can probably help you
I have a zip code dashboard created already, but it doesn't break them down by state because we haven't gotten that system table set up yet. Not sure how else I can easily separate those zip codes by state.
To set up Geo Areas you will also need to edit the stored procedure that applies them in SSMS. You set up what the Geo Areas are in the system table, and then you edit the stored procedure to define the zip codes. If you know SQL, it is pretty straightforward to update the procedure by looking at what is there. I suggest trying it in your Test environment first.
I have SQL but haven't played in it enough yet. I have a Solution Center pass for the Conference for them to teach me this (at least as much as they can in 15 minutes).
If you have the zips in Excel, say from a Tess Report e.g. User Defined Output saved as Excel: Excel, select the zip column, select Data, then geography, and it can pull the states using Bing.
That's awesome! I can save my Analytics Pivot Widget as an Excel! Thank you so much!
You know what would be even awesomer?
https://community.tessituranetwork.com/ideas/i/ideas/option-to-send-analytics-dashboards-as-spreadsheets-excel-csv?_ga=2.41457573.605272598.1690820408-2086228388.1678117458
(shameless idea plug)
Ok, so I tried this. The problem is only one computer in our entire building has Office 365 (don't ask...it's a little bit of a jailbroken computer), which is the only version of Excel that has the Geography setting.
Anyway, I was able to pull it using that computer; but now to send it to anyone who doesn't have 365 (which is everyone else in our building) the formulas aren't pulling properly. It's literally telling everyone that is still on Office 2019 that the formulas do not exist.
How do I get around that?
Try Excel tricks to get around it. Like copying the column of state names and pasting it as "values" into another column. So you are then not relying on the formula anymore but the actual state name that was generated by the formula. Then you can send THAT version to other staff.
100% copying to values only for shared excels! Any time there's a formula in a communal spreadsheet, there's >0 chance someone down the line will do something unintentional that borks it from there on, whether they have the latest Excel version or not.
I tried it for the entire sheet (I just copied it into a new workbook). And when I saved it and tried to open it on my pc (Office 2019) the same thing as before happened. So saving it as "Value" didn't work...is there another option?
I have no idea why it would do that if you pasted the "values". The only other suggestion that occurs to me is, after pasting the "values" is to save it as an earlier version of Excel workbook. Like maybe try saving it as the Excel 1997 - 2003 type. Beyond that, I am not sure.
Best of luck!
Try right-click on your states column and Data Type > convert to text