Separately calculating data by seasons

[petercreateParticipant]

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.

[dgteamModerator]

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.

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.

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.

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

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.

 

Does this help?

 

[Jean-Yves Le StangParticipant]

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.

[petercreateParticipant]

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

[Jean-Yves Le StangParticipant]

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.

[petercreateParticipant]

Kudos to you!

[petercreateParticipant]

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.

[Jean-Yves Le StangParticipant]

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.

[petercreateParticipant]

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.

[Jean-Yves Le StangParticipant]

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.

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

[Author]

Posts