Name:
Foundation Class 10 Working with COUNTER 5 reports in Microsoft Excel
Description:
Foundation Class 10 Working with COUNTER 5 reports in Microsoft Excel
Thumbnail URL:
https://cadmoremediastorage.blob.core.windows.net/23262281-4f7b-48cb-a561-10906ba5000d/videoscrubberimages/Scrubber_1.jpg?sv=2019-02-02&sr=c&sig=%2BJciQIx5ofVnyn0MGOwurbUbKIBkx8cENGwEJV1TeOs%3D&st=2024-11-23T09%3A27%3A00Z&se=2024-11-23T13%3A32%3A00Z&sp=r
Duration:
T00H23M46S
Embed URL:
https://stream.cadmore.media/player/23262281-4f7b-48cb-a561-10906ba5000d
Content URL:
https://cadmoreoriginalmedia.blob.core.windows.net/23262281-4f7b-48cb-a561-10906ba5000d/COUNTER Foundation Class 10_ Working with COUNTER 5 reports .mp4?sv=2019-02-02&sr=c&sig=xlmJuc%2BQmZbJGxYJsA%2BfBtR5TALV5JgFSbwI%2BUs2PIQ%3D&st=2024-11-23T09%3A27%3A01Z&se=2024-11-23T11%3A32%3A01Z&sp=r
Upload Date:
2022-02-04T00:00:00.0000000
Transcript:
Language: EN.
Segment:0 .
Welcome to the 10th COUNTER Foundation Class working with COUNTER 5 reports in Microsoft Excel. Please excuse my accent and all the noise from my keyboard. This tutorial covers several topics, from beginners to more advanced. It uses real data, which comes from a demo account which aggregates usage across many Wiley customers, which is an uncontrolled sample, which customers go in changes over time.
So you may see some general trends, but the numbers themselves are not meaningful. First, you need to load the data if your counter reports open without any issues, then you can skip this step. It certainly is not a mandatory format in COUNTER because reports can get so large that they will not fit in an Excel sheet. The mandatory format TSV tab-separated values, which is more robust than CSV comma separated values.
Unfortunately, Excel does not recognize the TSV file extension. Therefore, to open report, you need to ask it to show all files instead of all Excel files. By the way, if you are not sure where your report landed when you downloaded it, most likely it is in your downloads folder. Once you have your report and click on it, the report may either open directly or the text was at may open.
This is also a good option because it will give you control over all the columns in the report. Make sure that you choose UTF 8 as the filing holding because this will preserve all your special characters. UTF 8. This is also the place where you can deal with large reports. Should you ever end up with a report that is longer than one million rows? First thing, of course, you should ask yourself, do I need all these rules?
So I changed my filters? Or do you really need to work with this in excel? But if you think you must? Here is the place where you can import the report in two batches. Start the first one at row 1 and the second one at row 1 million and one on the next step. You can choose your delimiter. Make sure you have only the tab selected, and on the next step, you can decide whether you want to leave it up to Excel.
To decide, sell, buy, sell. How to format it. Or if you want to bring whole columns as text or a state or skip them altogether. This is a very safe method to open reports. If you always want to open the Reports with the text result, you may need to activate this. Go on to file options data show legacy data import results from text.
Then next time you want to open your report. Don't go onto file open. Instead, go to get data. Legacy results from text. Of course, it will then look only for text files to get all files and picture report. One other thing that can happen when you try to import your data is that Excel will try and get your report formats for you.
Unfortunately, it's not very good at that. You can still choose the encoding and digital emitters, but you don't have any control over the format of the individual columns, and you may even end up with all numbers formatted as text so that you cannot do any calculations. Therefore, if you have any problems opening your report in excel, always use the text without.
Now, let's work with the doctor. The good thing about life is that you can customize your reports. So that they contain only the data that you need to choose one of the master reports. Pick your metrics and breakdowns and exclude everything else. The consequence of this new flexibility is that it is no longer possible to automatically add totals to all the versions of all the reports, but totals are important.
So how do you add them without adding apples and oranges? One way to do this is to add filters. You highlight your report body and go on data filter. You can now select the single metric type, for instance. But if you want to add sum across all these rolls, this would still include all the hidden rows.
So it is not a good idea. You need to copy the filter report somewhere else. First, highlight the report body. Depending on your computer settings, it will select only the visible cells if you hit Copy. If it does not do that. Go to whole find and select special visible cells, only copy into another sheet and you have a clean field of report.
There's also the advanced filters, which will give you additional options. Something else might want to do is add the report totals right here into this report at the top. There's enough space. You take your kilometers. Copy them up and one instance of each value for which you want the total. Then you call up the sum IF function.
The function has three parts the look of range in this case, this is clunky starting at row 15, and it does not work to make the range a little longer. Who knows how long next month's report might be? All the blanks will be ignored. I need to also give it the location of the value that I actually look for in this case. Cell K2 and I need to tell it where the numbers are, and in this case, they are in full cell and start at row 15.
And again, you can add them to the number here. If I want to copy this formula into all the other cells, I need to add dollar signs to those elements which need to be stable. OK will always be to look up range and it will always start in row 15 lengths. I don't mind my lookup value in this case is also always for OK.
The role will vary the numbers I have in multiple columns and et cetera, but they will also start always at row 15. And now I can copy this function into all the other cells. There we go. We can do the same for the axis times.
Hopefully, the formula. But in this case? We will have to look up the values, not from column k, but from column J. You can also convert your report into a table. I like to report body and go on to Insert Table.
This will give you the filters and the table tools, which include a number of design options. This can be very practical. If your report is already very close to the final format that you would like, and if you're looking for a pretty design report, which you need to circulate regularly. If you really want 2 drilled deep into the data, the most practical tool is the pivot table.
To demo this, I have requested here a very large tidal master report with multiple metric types, multiple report filters and multiple attributes to show to create the pivot table. You highlight your report body and go into insert pivot table. This will usually go into new sheet. Here, you know, see all the columns from your report and you can decide which of them to drag into which of these fields here into the values field.
Usually you will want to drag. No now you have here the total usage from this column. First thing you want to do is break this down by metric type so that you don't add up apples and oranges. Simply drag the metric type into the columns or the rows area. And there you go. You can also break down by data type and perhaps by axis type. You can format these numbers by going onto value field settings and choosing number format and for use it.
Usually, you don't want any decimal places, but you do want the 1,000 separate us. You also see here the alternatives, which could request instead of the sums, lots of statistics plus the count of records in your report. I should, of course, also be getting rid of the grand totals here because I'm still eating up apples and oranges, so I go on design grand totals on four columns only by default. Excel will add the sub totals, and it will organize the table with one column that contains all the header in the sort of layout design.
This can be practical, but at other times it may be more useful to lose another design, which you can find on the Report Layout tabular form. And you can also ask it to repeat all item labels, which is practically if you want to export later on and to remove the subfolders. You may decide that it is perhaps not so practical to have books and journals in the same report.
You could simply move the data type into the report filters area. Now you can look at the data types one at a time. Exxon has its own ideas about how wide the columns should be. You can change that. But if you make a change to the report, they will go back to their original ways to stop this. You can right click in the table or pivot table options and disable the auto fit language.
I will now bring in the year of publication by opening. There's another column you can sort this, so that's the latest stock values on top. You can also filter here. You may also want to see percentages. You can do this by dragging your usage total into the table once more. And first, you have a duplication, but you can click on right click.
Value field settings go to show values as. And here you have a lot of options. Please try them all out. In this case, the most practical is of parent row total. So now you see the development of percentage of 08 usage over the years. Please don't pay too much attention to the numbers. As I said, this is an uncontrolled sample.
In this case, it seems to show a realistic trend, but this is a coincidence. You can override the column headers, but you cannot change the data any changes that you want to make. It is always better to do that in the raw data. For instance, the year of publication, if you want to spell that out, you do it in the raw data.
Go back to your pivot table and hit analyze refresh. And because you change the name of a field that was part of the report, you have to know, bring it back into the report and also redo your sorting. How do you compare to reports, for instance, 2019 versus 2018, when the 2018 count of format to demo this, I have here a j of one report from 2018 up to September.
And for the same time period, the count of five report where I have filtered on the total item requests. This is the metric that corresponds to the full text article requests encounter four. You can get this data from a total monster report, but also from the TR3. You cannot use the tier one because it excludes the open access usage. Let's say I am only interested in the reporting period for now, so I will hide all the other columns for both sheets.
No, I can bring the data from 18 into the 2019 report with the veruca function. This has several components. I need to tell it which value I wanted to look up, where it should be looking for the data.
In this case, I highlight all the columns in my 2018 report, and I need to tell it from which column it should retrieve the data. In this case, this is column H. And because this is the eighth column from the left, I will enter a number 8 here and I finally need to tell it what to do when it does not find a match. And in this case, I want an error message. And that's it.
I can now repeat this for each of the rows, and I can add the headers here and change this one, maybe and now make my comparisons. This looks very easy, but in fact, it will work only under very specific conditions. Each of your identify our values may be present in both of the sheets only once. Otherwise, you will drag in you the duplicate or may omit some other rules also, or the values must be present in both sheets.
But there may be journals without usage or journals which have been newly launched or seized or transferred, so you cannot guarantee that. So these are the rules. How to do lookup look up properly instead of using the raw data, create pivot tables for both of the reports that you want to compare. I have already done that here, and I have aggregated towards the wily proprietary identifier.
I have cheated here a little and added Wiley to the identifier, which was not mandatory in account before, but it's COUNTER 5 You have now exactly one row for identifier when you break in the usage. I have also done this for count of five. And then we look up that brings in the usage from COUNTER 4 to the COUNTER 5 report.
Now you will find some cases. Where you have missing values and this will be titles which are present in the 2000 19 report, but not been the 2018. So you know that you will have to copy over this report into the 2018. And the unit at that time was 0. No you don't get the missing value anymore.
If you do this for all missing values on both sheets. Only then will you be complete. This is all quite a bit of work. Fortunately, there is another method. Go to your report and add another column. I will call it year as in year of usage for this report, the year of usage is 2019 and I will now simply copy over the data from my other report. All I need is the identifiers.
And think about it. And all this usage comes from 2018.
I cannot create my pivot table. Drag in my identifying. My it and split it by year, and now I can make my comparisons. Have you noticed how easy it is today to retrieve sushi reports?
Just put you into your browser and get your data. So URLs are constructed following a certain pattern and you just need to concatenate all the components you have to base. You are in the report that you want because the customer, the and all the dates in place of monster reports. You would also add the filters and attributes the concatenation of all of these Follow the simple principle.
So a Sylvie and you connect them with the ampersand. This way, you can construct a whole new seek very spring out of these components here. You can populate the street with queries for all the reports from all your publishers and perhaps even write a little macro that will keep your data for you.
You can also try the following go to Donta from web. And insert the query right here. Whether this works depends on your computer settings, your version of Excel and the network that you are in. And I don't know what else in this case, it worked, although I've got an exception. Of course, I put in a fake customer ID, so I don't get any data.
If the direct import does not work, you can still copy the URL into a browser. We would then need to save the report somewhere. I have prepared something for you. You would get the safety report from data, to get data from file JSON.
The report header and the body come in different units. You click on it. This really leaves the body and break down as soon as you see only record items here, you can convert the data into a table. And now you have column where you see this little expand button here.
This means there is a lot of more data behind it. We'll consider it or just choose them all. And any time where you have a column with this expand button, you can click on it and expand. Expand to new roles will create a new role for each different item it finds extract values will put them all into one string concatenated through the delimiter, which you can specify.
I couldn't expand further. And further. This one is not complete. It's like the next one. More and now already I see my daughter.
So, yes, this is the principle how you import JSON data into Excel and then we continue as usual to filter whatever you like. Thank you for listening. There are many different ways to do the same thing in Excel and many more options and functions. We hope you found this useful. Please visit our counter YouTube channel for more Foundation Classes and come to our website.
Projectcounter.org above all, for the Friendly Guides and other materials.