 for the last six months instead of twelve months. This meeting is being recorded. So the first thing would be let's change the period instead of last 12 months to last six months. So I'll have to go to period, then deselect last 12 months and select last six months. And then I still need to have this data. I have to see the contribution of each month instead of just having the overall figure for the for the for the rest of the six months. So I'll drag and drop it here because I need them in terms of columns. By doing so, you could now clearly see how many HIV tests were performed for each month for each district. Okay, but again, this information, the way it has been captured in in data entry, it's been captured in terms of gender, female and male. Okay, so what do I do when I need, I need to further break down these figures into male and female. Let's say it's the HIV test performed. I need to see them for September 2021. They are one thousand nine hundred and seventy eight. How many were males and how many were female? Because it's not specifically for this, but rather for for for the entire figure, not just one, this one thousand nine hundred and seventy eight. So the first thing will be remember these table comprises of data elements as well as the indicators and the indicators that have been configured in a manner that they just calculate the percentage of the total. So before I break down this, I need to make sure that whatever that I've selected in the pivot table abides to the disaggregation of sex or gender. So first, before I add in my disaggregations, I'll go and update the data dimensions. So I'll just I'll just concentrate on one. Let's say just the HIV tests performed, then again height. Now that I have only one data dimension, which I'm sure that these these these values have been captured with the disaggregation of female and male. So the next step would be go and assign these disaggregations. So under under these disaggregations, unfortunately, the system cannot categorize in terms of what are the categories and what are the organization group sets. They're all available here, but you cannot differentiate them because they don't have maybe like an icon that could tell this is a category option. I mean a category or this is a organization group set. So you just have to know them by by names. And the organization group sets are more less like you have you have facilities. You have from the organization unit hierarchy, you could choose to make groups. Let's say, okay, I have the facilities, but within facilities I have different different type of facilities. I have the health centers. I have the hospitals. I have maybe the dispensaries. You could you could create a set that would differentiate them in a manner that once you need data in terms, let's say just the dispensaries by using that organization group set, you could clearly retrieve data just by dispensaries and ignore the other type whether they're health centers or the hospitals. So first I'll just go and assign sex because I want my data in terms of female and male. So if you click here, sex, it will ask you first, where do you want to assign this dimension? Should it go into column? Should it go into rows? Or it should go into filters. Okay. So with this data, I need them to go into into column. So I just click column and by clicking column, it will open up this kind of window and it will give you the options that are that are available under under sex. So with manual selection, you could do the selection and the arrangement depending on what you want to come first. So it's either you need the female or male first. You could always select and the way that you have selected and the arrangement was determined on how data gets rendered in the pivot time. Or sometimes you would find this manual selection also allows you to select probably only one one desegregation. If at all you're only interested in knowing how many were female instead of male. So since our interest is to have all of them, I could click this double arrow. It will automatically select them all with an arrangement of female first then male. Alternatively, I could click first female, I mean male then followed by female. That means even the arrangement will start with male or female. Or the other way round, it could be sometimes you have probably selected male first and female second, but you need the female to go first followed by male. So you could just click here. If you click here, it means it has been selected or highlighted and you could use these arrows here to move up and down. So since it's female comes next, you could see that you cannot move it down anymore because it's the last one in the sequence. So I'll just shift it and move it on top of male then click hide. So if you click update, remember we had deselected the HIV test positive and of course the indicator that completes the positivity rate. So for now, with this kind of arrangement, I have my HIV test performed, but in terms of last six months by each month, that means it's from the last six months starts from February or the way to September 2021. But again, I have further broken down the data of each month by female and male. Now, if you have this kind of report and you have this blank data, would you really want to have it presented the way it is? The answer would say probably no. So instead of persisting these empty, empty cells, you could always go into options. If you click there, we have a couple of options under the option window. We have the data, legend, series, style, limit, values, and parameters. So with data, which is default selected as the first one, since it's the first one in the arrangement, we have a couple of items. We have the data dimensions and the rest. But this time around, our interest is to only omit the empty cells. So we have a section here, empty data, I could always click hide empty columns. It's going to hide all the rows that are empty, but if at all you need also to hide the empty rows as well, if you tick those, then it's going to hide all the rows and columns that are empty. So, but still from the way here, if I want to see so overall, how many HIV tests were performed in the month of September 2021, or by each month, how many, how many totals would I want to see in terms of gender, but also by district in overall, how many in total where these HIV tests performed. Again, you could always go back to options and under options, we have a section for totals. This is where you could check and by checking that means you're telling the system to give you the totals. And the totals could be in terms of columns as well as the rows. And not just the overall totals, but we also have the sub totals. So let's go first with the overall totals. I need my data to give to have a data in terms of columns. And if you go back here into this table in terms of columns, that means you're requesting to have in terms of how many total females, how many total males, and of course, the sub the rows, it's going to be my district. So if you select column totals, row totals, and click update, that means you'll have these kind of totals. You'll be able to say, okay, from this table, how many HIV tests were performed for the bad district. And of course, under the month of September 2021, how many HIV tests were performed for specifically female? You could also have it from from this column, column total. So that is one. But sometimes from this table, I would want, okay, I need my values, these figures to be sorted. You could always use these arrows here beside the female dimension. So if you click that, that means your data, they're going to be sorted in terms of the smaller figures to the higher and the other other way around. So if you click that, then it's also going to sort them in terms of the highest figures, high to low or low to high. It all depends on how you want the figures to be sorted. So that is the way you could play around with the options. So first of all, we can save this. Let's go and save. By saving this, you could go to file. Now you could see there are two, two save buttons. One is saving it as a new. And this one is more less of overwriting what is already there. So I'll go with save. Say save. And name would be, now for the purpose of this academy, I could just prefix it. My name initials, because we are more or less looking at the same, same information, but with different people, if we have them all saved with the same name, then the system won't accept because the name has to be unique. So I'll just prefix it. However, in real world, you don't have to use this kind of prefix. And again, with saving, always make sure that you incorporate the three dimensions that are available in this table. So that once you look at the name, the name of the visualization, you could clearly tell or understand what is being analyzed or what is being presented in that table. So for this table, we are looking at HIV tests performed for the last six months in by district in what, in animal region. So you could click find, save, then I'll say, let's take a M, sorry, M, let's go. HIV tests performed last six months by district in animal region. If you have more detailed information, you could always attach them under the description. Once you're done, you could save. So that means even if you log out the system, you could always come back and retrieve this, this kind of table. I hope that that makes sense. So I'll go and create another visualization because I want us to see even the concept of having the subtotals in terms of columns and rows. So I'll go here, click new, then go make sure I select pivot table, go under tab. Now I want the same data element selected, then go to period, I'll go and select years. Instead of last year, I'll go and select years as period type, then select last five years. I'll also click hide. And then by organization unit, I need my data in terms of facilities within within the bad district. So by default, training land it's ticked, but since I need my data by facilities and not all facilities within the training land, but rather all the facilities within the bad district. So I'll have to navigate and look for the bad district, select bad district, but deselect training land. But since I need all the facilities, instead of going through and ticking all the facilities, I'll use the level, click facility. So I'll also click hide. Now, if you click update, it's going to give you this kind of this kind of table. But again, I need to see my data by facilities. But from here, I could see the overall sum of HIV tests performed regardless of which facility. Remember, you always have to rearrange and make sure that these things are a shuffle. Then if you click update, now I could see in terms of of different facilities. But I still need to see this data further broken down in terms of gender, sex. So I'll always go select sex, have them all selected. And the column, of course, if you click update, now I have them like this. But if you look at this table, is this a better way of representing the data? I would definitely say no, because you could see that we have these periods repeated under each health facility. So instead of having them like this, again, I could take the period and have it arranged under the column as well. So if I click update, you would always see now the data at least make sense. Now I'll go and hide the empty rolls and columns as well as show the totals and overall totals. If I click that, now I have this kind of this kind of date, which is more or less the same like we had previously. But with the health facilities that I've selected, I need them grouped in terms of the type of facility. From this list here, you could see we have the PHC, we have the health centers, we have the dispensaries. But if again, this is because this is the only that is available within the hierarchy. But if we also had hospitals and so many other type, you could always come here into type. Now this is the organization unit group set. If you click type, you could always again have to specify the type where we want that further desegregation of type to be assigned to. Is it column rows or filters? So I'll go with column. I mean with row, why? It's because this is more or less to further break down the selected health facilities. And I have my organization unit, which are facilities arranged in rows. So my interest is to have the PH, have the dispensaries, and of course the health centers. So I have selected only this and ignore the rest. So if I click update again, you could see the arrangement. This is it's clearly a health center. But again, because I've selected a further dimension type as health facility, I'll have this kind of arrangement, which again, it doesn't make sense having them like this because it's they could be repeating throughout throughout the table. So instead of this, the better way would be reshuffle this so that the system could present them in a group. So if it will check, if it's a PHC, then it put a group of PHC, then in front of PHC, it's going to give you a list of all those data, PHC. So by reshuffling these dimensions and let organization unit group group set come first before the organization unit. If you click update, now you could see my data. All the facilities are still there, but they've been grouped in terms of the type. Now I have the overall totals in terms of rows and columns. But with this, since I have different type of facilities, my interest would be to see the total for each type of facility. How do I do that? Now it's when it comes back the concept of sub-totals. So you always have to go to options, then say the sub-totals, but it's the sub-totals of what? The columns because we have them, the data is listed in terms of columns. So if you click column sub-totals, if you tick that and click update, now you could have the sub-totals for each type of facility. I could clearly tell how many in 2018, how many female were diagnosed with for the PHC type of facility as well as the health center and disparities. So remember that we this table, it's not always, I mean, I won't be able to retrieve the same information. Instead, I'll have to go back and do this kind of selection and analysis from scratch. So instead, I'll just go self, I'll say KM, test, performed, last five years, a facility and let's say six. So I have this kind of data, but sometimes I'll just take you back to options here so that we could also go through the other remaining kind of data options that are viable. For instance, you would want to see the dimension levels. If you go back to this, you could, you cannot tell what are these. Of course, you understand the organization unit because you have selected them by yourself when you're creating the table. But if you just have this kind of table presented to someone who doesn't know where is this data coming from, probably it would be difficult to know what are these in columns. So you have an option of clicking. If you click dimension levels, that means the system will be able to tell you what are these, whatever that you have here, this is data, what you have here, it's period, what you have these organization unit and sex, which is the further dimension. And of course what we have in here, these are the type, organization unit type. So that is one, but we could also do, we have this functionality of skip rounding. This is mostly used and valid when you're looking at monetary figures. If you're dealing with money, you wouldn't probably want to round off staffs because rounding off could end up creating or giving wrong, wrong information. So if we were dealing with money, we would probably choose to skip rounding. So that means if you had data in terms of decimal places, then they wouldn't be round off. So we also have the aggregation type. This is more or less used to give out the figures in terms of what if you have, if you want to present the data in terms of average or just the last value or the count, this is where you could also define an aggregation type. From that, we have this number type, which gives the percentage of raw and percentage of color. Or they could just be values. But again, this is only valid indicators, which are calculating data in terms of percentages or the indicators itself. You wouldn't want to do this kind of selection in terms of percentage because they are already computed values. But with raw data, like the ones that we have here, you would probably wish to see out of for this, for this 1000 and 21 number of females. What's the contribution in terms of percentage out of, like, let's, let's go here for this health center. We have 1000 and 21 females for the year of 2018. But what's the percentage of this figure out of 7,220? So this would be, you would want to see the contribution by percentage out of, out of the entire figure that is available in this health center. And I'm saying health center because I want us to check the percentage in terms of rows first. So what we have in rows is the organization unit. So you'd go under options. You have number type at the moment or by default, its value. But if you want to see the percentage, you could select percentage of raw. Of course, if you click update, that means it's going to give you a percentage of what? For the 1000 and 21 figure we had, if you compare it to the 7,220 total for all the year, for all the five years that we have selected, then that is only a 14.1 percentage. And of course, if you do that, that it doesn't make sense to have these photos. So that is, if you want to look at the contribution of each value that you have by rows, which is my organization unit. But also you have the percentage by column. So if you select that, it's also going to calculate the percentage of each by these columns. So whatever figure that we had here, the 7.3, it's a contribution out of the overall total HV test female we had. So that is another way you could further do the analysis with the parameters that you have selected under the pivot table. Okay. So I'll also, we remained with six minutes before we go to break. I think we can use these six minutes at least to also try replicating, create a pivot table that shows this same information. So it's now five, let's use those five minutes. And if you have any challenges, please ask. We'll be happy to assist you. So let's go to the pivot table, create a pivot table that is looking at the HIV test performed for the last five years. Disaggregate them by gender, I mean sex, female and male. And of course, the organization units should be all the facilities and a bad district.