Category
Report Builder
Product Version
8.0
Convert Day data into Month
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
The same question
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 -
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' -
and then grouped the data according to the months by using BETWEEN first date of month to last date of month -
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
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 -
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' -
and then grouped the data according to the months by using BETWEEN first date of month to last date of month -
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
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 -
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) -
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) -
4. Here I have added in another field - Invoiced amount, which shows each sum that was invoiced for that particular date -
5. Here I have gone into edit format on the invoiced date again to 'suppress duplicates' -
6. This then shows the month once, but still shows the invoiced amount for all dates in that month -
Is this what you were after? If not please let me know! Though, I do hope it has helped!
Best Wishes,
Lesley
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 -
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) -
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) -
4. Here I have added in another field - Invoiced amount, which shows each sum that was invoiced for that particular date -
5. Here I have gone into edit format on the invoiced date again to 'suppress duplicates' -
6. This then shows the month once, but still shows the invoiced amount for all dates in that month -
Is this what you were after? If not please let me know! Though, I do hope it has helped!
Best Wishes,
Lesley
Replies have been locked on this page!