Separately calculating data by seasons

DataGraph Forums Technical Support Support Desk Separately calculating data by seasons

Viewing 10 posts - 1 through 10 (of 14 total)
  • Author
    Posts
  • #14353
    petercreate
    Participant

      How to use pivot command to calculate data by seasons? For example, for spring from March to May, summer from June to August, and so on. The current options in the pivot command’s map rows don’t allow seasons.

      #14356
      dgteam
      Moderator

        Hi Peter,

        That would be a nice addition to the Pivot command.

        For now, you would have to map the months to seasons.

        Here is an example where we have a time series, but we want to average the values depending on the season.

        Data

        Here is how we did this:

        Step 1 – Create a look up table that shows the season for every month, using the meteorological seasons. Also add a look up table that maps the season number to season names.

        LUT

        Step 2 – Add an Expression column and use the month function to create a column that shows the month number from the date. Right click on the header of the month column and select Map > Season.  That will create the Map column for the season.

        Here’s what your table will look like after creating these two columns.  You can create the map column manually but it’s much easier to write click on the column header, you just have to make sure that the columns you want to map are named the same in the data, and the look up table.

        Map2

        Step 3 – Select the data columns Season and Value, and add a Pivot command.  That will group the data by season.

        Map3

        Step 4 – if you want to show the names of the seasons, change Map rows to ‘From Column’.  Then use the season names look up table as shown.

        Map4

         

        Does this help?

         

        #14357
        Jean-Yves Le Stang
        Participant

          Hi,

          As seasons do not start with the 1st day of a month, for better accuracy you can use the “dayofyear” function instead of “month” (provides the # day of year for the Date) and then map the day of year using the “intervals” method, and proceed as suggested above.

          This sample is mapping date to season based on a 21st (March, June, September, December) changing date.

          Seasons2

          #14358
          petercreate
          Participant

            Helpful! By the way, which function did you use to generate the date column?

            #14359
            Jean-Yves Le Stang
            Participant

              I created an Expression Column based on the row number (#) as shown below.

              Times in DG are seconds and this is the reason why the column number is multiplied by 864000.

              I made it for 1970 but you can store the 1st Jan of any year as start date in the global variables section and use Dstart+(#-1)*86400 to generate the Date column. This does not change anything as the 21st of March is the 80th day of the year whatever the year (except leap years !).

              Hope it helps.

              Seasons3

              #14360
              petercreate
              Participant

                Kudos to you!

                #14361
                petercreate
                Participant

                  Following up the current question, the current solution is to do the sums for all four seasons, separately. What if the data is required to be calculated on seasonal basis? That is to say, data is calculated as time series on seasonal resolution.

                  #14362
                  Jean-Yves Le Stang
                  Participant

                    Hi,

                    Not sure to understand your needs. Can you provide a sample screen capture and explain what you expect once the season has been determined from the Date column.

                    #14363
                    petercreate
                    Participant

                      Hi Jean-Yves, for example, the x axis may show the sequence as 2020 winter, 2020 spring, 2020 summer, 2020 autumn, 2021 winter, and so on, instead of the calculated results from all winters, summers, … Sorry, I don’t know how to show an example here, because of not knowing how to make this happen.

                      #14364
                      Jean-Yves Le Stang
                      Participant

                        Noted

                        I have created a 5000 randomised list of dates (between 1st January 2020 and 31st Dec 2021(column “Daie”), and a corresponding randomised list of values between 0 and 10 (column “Value”). Note that Date column is not sorted but it has no impact on further operations. I assume this is what your input data looks like. I have generated the season for each row as explained before.

                        If you want to incorporate a year information into X axis labels, you first have to extract this information from the Date column using the “year” function of DG just like you used the “dayofyear” function to extract the season (but it does not require any mapping).

                        Then I suggest two possibilities

                        The first one is:

                        • you create a text expression column (SYLabel)  using the drop down menu of the column definition section This is to concatenate year and season in one field.
                        • You create a two entries Pivot using SYLabel as Rows and Value as Value, as shown below.
                        • The result looks to what you explain but I see two issues with this option.
                          • the first one is that the values on the horizontal axis are not sorted in their relevant order. So far, I could not find a way to sort them as required. May be DG team can help ?
                          • The second is that as X axis labels are wide it will no be possible to display a long period of time on the same graph. May be that using the “Y standard” format for the graph will bring some improvement.

                        Seasons01

                        The second one is:

                        • Once the year is extracted, you create directly a three entries Pivot using Year as Rows Season as Columns and Value as Value, as shown below. No need to create a text expression and easier to cumulate several years on the same graph. A special short test columns has to be created to sort the seasons as required, using the Map Columns menus

                        Canevas 2

                      Viewing 10 posts - 1 through 10 (of 14 total)
                      • You must be logged in to reply to this topic.

                      DataGraph Forums Technical Support Support Desk Separately calculating data by seasons