Convert Day data into Month

Alexandra Aristova shared this question 3 months ago
Answered

Hi,

Please, help me to solve the following: if I have a Value data for every day in a year and want to convert it to be seen in the table report as Month + sum of the value for the whole month, what should I do?

Regards,

Alexandra

Best Answer
photo

Hey Alexandra,

I am very sorry for the delay in this but I did tell myself I was going to get an answer out to you before I finish today! :)

Right, so you only want the total amount displayed for each month.

Richard is correct, we do need to group the data.

This is what mine looks like after grouping -

0f69dfd9e0ec6a3b9e7fdec9ebd572a8


I did, however, choose another approach - I personally decided to bypass the whole calculated field route, I always feel if there is a way to create it without making new fields then I wont - though, it doesn't mean Richards approach or suggestions are wrong, we just have different ways of doing it :)

So all I did was click on the drop down arrow of the field and selected 'Group Data' -

e91d9c669e22f08cb252f5f6f48619b4


and then grouped the data according to the months by using BETWEEN first date of month to last date of month -

d45fdaa44512a8322795b4eecb96c69b


And that was it! :) Once you have grouped the data, the invoiced amount column automatically added the sums together to make the total sum for each month (group).

Let me know if this is what you were after/ if this has helped or not!

Best Wishes,

Lesley

Comments (1)

photo
1

Hey Alexandra,

Good question!

Ok so in the below screenshots I explain how I have formatted the invoiced date field, to months, and then I add in another field for invoiced amount to show the sum that has been invoiced for each month :)


1. Here I have added the invoiced date field and as you can see, this has specific dates for each month -

4c1ae9ec5ebf4b5d315fd4c16c6468de


2. Edit the format to display month, rather than day, you can enter this as full month name, month number or customise as you like - I customised as MMM (short month name) -

a2126edc04a95305ce313b907e1f47c1


3. Here you can see this has applied to the column, but it is still showing a month for each of the dates within that month (we will suppress this later to only show the month once) -

92a6165a50c1076c58256f23b458c988


4. Here I have added in another field - Invoiced amount, which shows each sum that was invoiced for that particular date -

c93aa0741e98c118d902c9350380afd3


5. Here I have gone into edit format on the invoiced date again to 'suppress duplicates' -

e2bf45a846f0e564a9ea1f4c744bd210


6. This then shows the month once, but still shows the invoiced amount for all dates in that month -

0a02a6de166b390f039a60f69895f4c8


Is this what you were after? If not please let me know! Though, I do hope it has helped!

Best Wishes,

Lesley

photo
1

Hello Lesley,


Thank you for taking care of my question. The main trick is a little bit further - when we have Month but we also need a "sum of the value for the whole month". In your example it will be to have April with invoiced amount as $44,267 and so on... Do you follow me?


Regards,

Alexandra

photo
1

Looks like this link is related to my question but still not giving me a full understanding....

photo
2

Hi,

I think you are looking at a GROUP BY Convert(date, <datevalue>) option. You can use a calculated field to achieve this (as a workaround). This field can then be used as a report column.

Regards,

Richard

photo
1

Hello Richard,

Can you be more detailed about using calculated field for this? The main thing here is to get values summed under each month after the "date conversion".

Regards,

Alexandra

photo
1

Hey Alexandra,

I am very sorry for the delay in this but I did tell myself I was going to get an answer out to you before I finish today! :)

Right, so you only want the total amount displayed for each month.

Richard is correct, we do need to group the data.

This is what mine looks like after grouping -

0f69dfd9e0ec6a3b9e7fdec9ebd572a8


I did, however, choose another approach - I personally decided to bypass the whole calculated field route, I always feel if there is a way to create it without making new fields then I wont - though, it doesn't mean Richards approach or suggestions are wrong, we just have different ways of doing it :)

So all I did was click on the drop down arrow of the field and selected 'Group Data' -

e91d9c669e22f08cb252f5f6f48619b4


and then grouped the data according to the months by using BETWEEN first date of month to last date of month -

d45fdaa44512a8322795b4eecb96c69b


And that was it! :) Once you have grouped the data, the invoiced amount column automatically added the sums together to make the total sum for each month (group).

Let me know if this is what you were after/ if this has helped or not!

Best Wishes,

Lesley

photo
1

Hello Lesley,

This is a very good example and explanation! Eventually, everything is summed and looks as we want.

Thank you very much!!!


Regards,

Alexandra

photo
1

Good Morning, Alexandra!

I am pleased you have got the results you wanted! :)

I will go ahead and mark this ticket as complete, but if you do have any further questions, please let me know!

Best Wishes,

Lesley

photo
1

Yep, thank you!

Regards,

Alexandra

photo