DataGraph Forums › Technical Support › Support Desk › Separately calculating data by seasons
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.
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?
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.
Helpful! By the way, which function did you use to generate the date column?
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.
Kudos to you!
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.
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.
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.
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:
The second one is:
DataGraph Forums › Technical Support › Support Desk › Separately calculating data by seasons