 Okay, good morning, good afternoon, good evening everyone. Welcome to the day four of the EHIS-2 Digital Analytics Tools Academy. So, today we are going to have a very interesting topic that we'll be covering, which is the pivot table. While it is an interesting topic, there are a lot of content area to cover. So, unfortunately, we won't be able to wait until everyone joins, so we are going to start the session. First of all, one reminder, in case you have not been able to provide feedback for any of the following, any of the previous days, especially yesterday, please remember to provide feedback for the session. We are going to take the feedback very seriously, so we have gone through the feedback and understood a few areas that we might need to change in the way that we are delivering the content. So, especially one area that most of you have highlighted is to have more interactivity. We will try our best, but inherently in this online platform, there are challenges in having interactions the way we are doing it on site academies, and we have a level best to do that, especially in these co-analytic tools that we'll be covering in the next few days. So, today we are covering pivot table, tomorrow data visualizer, and then on Monday maps. So, these are really important topics and we sincerely hope that if you have any queries, you can ask them on Slack or if the facilitator gives you time, you can connect through your audio and ask. So, without taking too much time, let me invite Pramil from History Land Camp. So, he's kind of an expert in conducting this topic, he has done it for so many years. So, over to you Pramil. Good morning, good afternoon and good evening. Hope I'm clear and you can see my screen. Yes. Can hear me also. All fine. Right. So, as Pramil mentioned, we are starting the analytic tools component of this DHIS2 that is one of the four components that is the pivot tables, data visualizer, and maps and also dashboards. So, we will be starting with the pivot tables today, and tomorrow they will be doing the charts and then followed by maps and dashboards. So, these are very important topics and so I'll try to go slow and we will cover the most important things during this show. So, objectives for this today's session is, first we are going to describe what a pivot table is and then we'll demonstrate how to modify a pivot table and then how to create pivot tables in the app and also how to use the options and pivot table and how to use the categories and organize these applications and explain how different number types can be used in DHIS2, pivot tables and demonstrate how to apply legends to pivot tables and then explain how to add descriptions and sharing features and also how to do that. So, the session will be, there will be a small presentation which will be followed by demonstrations and after that we will be giving some time for you to practice the exercises in the DHIS2 instance. So, moving on to pivot tables. So, as the mention says, it's a table, it has rows and columns and it's a visualization type with rows and columns. So, anybody who is familiar with Excel and other spreadsheet tools will be, must have used pivot tables before and what pivot means, you can change the number of dimensions in the table. That is, you can change any number of dimensions, add them to columns and change them to rows likewise, you can pivot within the table. That is why we call it a pivot table. So, it is a good analytic tool and specially pivot tables are used when you have to analyze the larger chunks of data and also when you have multiple dimensions within your visualization, within your analysis, then you can use pivot table. Unlike in charts and maps where you can't do this, handle big number of data. So, moving in the presentation, so creating a pivot table in DHIS2 is simple. We have just three steps. First, you have to select the data, periods and the org units. That is the three dimensions in DHIS2 and then you can alter the table layout as you wish. And there are additional options which you can use in pivot tables to make them look like you want and modify them accordingly. So, just three simple steps but you have to remember these steps. So, moving back on to the basics in DHIS2, I hope everyone has undergone the fundamentals, online fundamentals academy before coming into this one. So, you must be familiar with the three Ws in DHIS2. That is what, when and where. So, these are the basic fundamentals. When you are creating any type of analytic tools, any type of visualization, whether it's a table, a chart or a map, you have to make sure that you know these things and you select these things. So, when you move on to what, what is the data we use. It could be indicators, it could be data elements, it could be reporting rates or it could be tracker data with individual data. So, that is what comes under what component and then in the when component, it's a period. So, there are fixed periods and relative periods where we will describe when we are moving on. And third component is where. That is the organization unit. This is where the data is coming from. So, there is user dependent org unit and also fixed org unit. So, we'll describe those also when we are doing the layouts. So, the first part, we will see what are the pivot table dimensions and how to modify these three dimensions. So, I'll be sharing the DHIS2 instance. Hope you can see it now. So, when you look on to the instance, this is where you land on the dashboard. So, where are we going to create the tables? So, if you go to the apps icon, the six nine square icon, you can see different apps which are available to you. So, now in the latest versions, the pivot tables and maps, sorry, pivot tables and charts are both embedded in the data visualizer app, which is right down here. Earlier, there was an app called pivot table. It is still there, but we will be using the data visualizer now to create both tables and graphs. So, even what you can do is you can search for visualizer and filter that app and launch data visualizer. So, when you launch data visualizer app, this is the layout we see. I'll start with opening a table so that the layout is much more clearer. So, to open a table, you go to file and then click open. Here you can see a list of tables and charts which are saved in the system. So, as I mentioned, both tables and charts both are now in the data visualizer app. So, if you see on the top, there's a box to filter to search what you are looking for. And there are two drop downs on the right hand corner. One is for all types. That is, you can see both tables and charts. But if you want to see only tables, you can click pivot table. Or if you want to see a particular chart type, which we will do tomorrow that you can also do. So, you can pick pivot table if you want and search for tables on the end. Then the owner. If you want to see chart tables created by you, you can filter it like that. Or if you want to see tables created by others, you can filter like that. So, I'm going to open this HIV testing table. When you type, it will filter and I'm going to open this first table. HIV testing performance by sub-pork units. So, when you click on that, you will see the output like this. So, now you can see that pivot table has been opened on your screen. So, any volunteers to describe the table which we see now? Anybody? Am I audible? Yes, you're audible. So, this looks like the pivot table for testing for the last 12 months in the sub-organizational units of animal region and food region. And we are looking at HIV test performed, HIV test positive and the test positivity rate. Thank you. Right. Thank you. So, as you mentioned, it's a table and of the test performed, the HIV test performed by, you know, by your units for the last 12 months. And the rows are the regions that is the organization units. And the columns are different, three types of data. That is one is HIV test performed and one is HIV test positive. And the third one is HIV test positivity rate. So, you can see rows and columns within this table. That is what you see in this white blank area, the table which we open. And if you move on above that, you see a place where columns, rows and filters are indicated. This part is called the layout, which we will describe later where you select the columns, rows and the filter. And above that, there are four buttons. One is to update. That is when you make any changes in the dimensions or options in the table, you can update it. Second one is file menu. So, there are different menus in that, different menu options in that. The third one is the options button, which you click to launch the options for the table. And fourth one is the download menu. That is where you download data. So, these things we will discuss when we are going forward. And on the top right corner, there is a place called interpretations. And that is about the right half of this. And if you move on to the left half. So, on top, you get this drop down where you select what type of analytic item you are going to create. We are focusing on pivot tables today. So, I'm going to select pivot table, but if you want to create a chart, which we will do tomorrow, you will be able, you will have to select the chart type. Then there is a list of dimensions which you can use for the table. There's a filter button also for the dimensions. And below that, we have the three main dimensions. That is data, period, and our unit, which we described earlier. That is the three Ws in DHIS2. So, here this is where we configure what data we want in our table, what for what periods and for what our units. And below that, there are other dimensions which you can add to your table. These things also we will describe later. So, we'll see how to modify our table. Currently, our table has three data items for last 12 months for two regions. So, let's see about the data items first. So, when you move on to the left, first one is data. You can click this data button, data menu, and launch this data selection dialog box. You can do this also from here, the columns, data. If you click that same thing will come, but we will use the left side menu for the moment. And when we click data, you can see the dialog box to select data as I mentioned. So, on top, first thing you have to select is the data type. Currently, by default, it's indicators, but you can select either indicators, data elements, or data sets, event data systems, and program indicators. So, in aggregate, when you have an aggregate instance, you will use the indicators, elements, or data sets. And these are for tracker programs. And when you select either one of these, then you have to select the group relevant to that. If it's indicators, you can select the indicator group, and you will see a list of indicators within that indicator group. So, what we will do is we will remove one of these data items. We will remove these data items and put them back. So, we have currently three data items. One is HIV test performed, then the number positive and HIV positivity rate. Can someone say whether these are elements, indicators, or data set items? I think the test positivity rate is an indicator. Then the HIV test performed is an element, data element, and the HIV test positive is also an element, which is to come up or to calculate the indicator for the test positivity rate. Yes, correct. So, the first two are data elements which are raw data captured in the system. And the third one, the positivity rate, when you see the rate or percentage or things like that, you can guess that it is an indicator. Because we don't enter the rates and percentages into the system, we calculate it using the raw data which we enter. So, as you mentioned, there are two data elements and one indicator. So, I will remove the HIV test performed, which is a data element and update the table. Now, you can see only two data items. So, I'm going to put that back into the table. So, you go to data, you select data elements and it's in the HIV group. And if you scroll, you can see HIV test performed is here. Or you can search it here also, HIV test performed. So, now we have to select this and put it to the right side. You can simply double click it. So, if I update it now, let's see what will happen. So, we see the same three data elements, but in a different order. So, that order is also defined here. So, earlier this was on top. So, if you put it back on top, now you can see the previous table with the same order. Let's see an indicator also. So, we will remove this HIV test positive 50 rate and click update. So, you can see that one column is missing now. We don't have the positive rate now. We are going to put it back. So, we go to data indicator. So, we guess that it's in the HIV group. And we'll search for it positivity. So, we have this HIV test positivity rate. We are going to put it back and update. So, now we see the same original table which we started with. So, we have learned how to remove data items and how to add those data items. So, next we are going to move on to second dimension, the period. So, we will see how to change the period dimension in the table. So, when you click that, currently what is selected is last 12 months. Let's say we want to change it to last six months. So, when you go on to the left side of the dialog box, you can see two options. One is relative periods and one is fixed periods. So, fixed periods it's easy to understand. So, you define a certain fixed period like January 2021, February 2021 or fall 2021. Likewise, you select a fixed period. You remove this one and you can apply 2021 here. So, you get a fixed period of 2021. What happens here is when you create this chart and save it and regenerate it any day, it will be the same chart because it will have the 2021 data unless the data is changed within the system. But in contrast, if you go to relative periods, the date, the reporting period, the data period of the table changes depending on the today's date. For example, if we create a chart table using this month and save it today and we get data for May 2021. But if we generate the same table next month, that is in July, we won't get data for May 2021, we will get data for June 2021. So, it's a relative period to the current date. Similarly, you can use relative years, relative days, relative weeks likewise. So, if we generate a table for this last year today and save it, we get data for 2020. And if we generate the same table in 2022, we get data for 2021. I hope the concept is clear, the relative periods and the fixed periods. So, why do we have this relative periods in the system? Who can answer that? Why do we have relative periods? What is the importance? Any main place where we can use the relative periods? I always want to see the last three months report. I always want to the fixed periods. So, in that case, we use the relative periods. In dashboards, we can see then it's convenient in using it in dashboards? Yes. So, the main use of this is in the dashboards. So, if you create a dashboard using this month, if you create a table using this month and put it in the dashboard, and when you come next month and just on the dashboard, the data will be updated. You don't have to do anything. So, it's period is relative to the date. So, the dashboards will be automatically updated. So, that is one place where this is important. So, I'm going to change this period to last six months. So, when you're selecting another thing to keep in mind is this grouping of periods. These are grouped according to the frequency. So, if you want months, you had to select months here. If you want years, last year, you had to select years. Same in both in fixed and relative periods. So, I'm going to select last six months. So, it should be months group and I select last six months. You can have multiple periods, but I'm going to select last six months and I'm going to update it. So, now the data has changed into last six months data. Right. So, that is about periods. And next, we are going to see how organization unit is working this table. So, we are going to click on organization unit and see how this animal, how did this animal region and hood region came into this table. Select organization unit. Now, you can see the user subunit is ticked and you cannot see anything else. The below table, the tree is blank because already user subunit is selected. So, there is a concept called user organization units. So, that depends on which user is logged into the system and using this app and what privileges is that user having. So, that means what organization unit is that user attached to when it comes to data analysis. So, if you select user organization unit here, the data for that user's organization unit will be displayed. So, if that user is attached to the national level, the table will show national level data. If you select user subunit, then one level below that, that is probably a province or maybe districts in some countries. It depends on the organization unit hierarchy. So, the second level, one below that user is attached, data will be shown. So, this one, third one is the third level. That is two units below that user. So, if you un-tick those.