How to make trend analysis by previous month sheets clubbed and show it in dashboard?

Sandhiya

I have the scenario, where I will make record for every month end and I need to club previous month reports at the end of every month, make a trend analysis to show it in dashboard.

Example, on 30 January, January month report generated and on feb 29, February month report generated, I want automation to club these two month sheets and show it in Dashboard.

How to achieve this, anyone kindly help me on this.

Thanks,

Sandhiya P

Genevieve P.

Hi @Sandhiya

The way to do this will depend on how your source sheet is set up, the one you want to keep a record of.

You could either set up a metric sheet with formulas to create a SUM (using a cross-sheet SUMIF formula) based on a date column (if you have one in your source sheet). For example, you could say SUM all the rows in this column IF the Date Column has a Month of 10.

Or, if the data will always be updating and you don't have a date column, you could use a Copy Row Workflow which will copy a summary row at a specific date in time to another sheet so you can keep this historical data. Does this make sense?

Let me know if you think one of these scenarios would be better for you and I'd be happy to help further. It would be useful to see a screen capture of your source sheet, but please block out sensitive data.

Cheers,

Genevieve

Sandhiya07

Hi @Genevieve P.

Hope you are good and safe..

I got your point..Thanks for that.

I have date column in source sheet, but here the issue is once particular month is over, then only we will get final data. So how we make the cross sheet method?

Kindly suggest me some solution.

Genevieve P.

Hi @Sandhiya07

Can you explain in more detail what the issue is? Do you update the same rows, or do you add new rows for the new month?

It would be useful to see a screen capture of your source sheet, but please block out sensitive data.

Sandhiya07

Hi @Genevieve P.

My dashboard should like below screenshot,

And my Master sheet will look like this,

I have the common sheet for getting data, in that sheet only, Admin Team will update data on monthly basis(manual process),but I can get the final monthly Sheet at the month end, because through out the month some changes will happen.

Once the month end sheet generated, we will copy and move the final sheet of the month to the new folder, from there the values should automatically populated into the master sheet shown above for dashboard.

Example: First we will have September month value in the master sheet, then the October month values should get entered in the next row and so on... for the dashboard.

Kindly suggest any other solution if possible.

Thanks.

Genevieve P.

Hi @Sandhiya07

If you are creating a new sheet at the end of every month to move your data over, then you would not be able to have the information from that sheet automatically populated into another sheet with formulas or cell links.

You would need to either create a new formula each time or a new cell link. This is because the sheet didn't exist before the end of the month.

However, you could have a Report set up to look at an entire workspace! Then when a new sheet is dragged into the workspace (instead of a folder), it will automatically be included in the Report. Where do you have the percentages housed in your Final Sheet of the Month? Are they in a specific Row, or are they in the Sheet Summary data?

Cheers,

Genevieve

Sandhiya07

Hi @Genevieve P.

Thanks for your reply.

From the final sheet of every verticals I will calculate total in Master sheet. From that total value, I'm calculating percentage.

Genevieve P.

Hi @Sandhiya07

In this case, it sounds like you will need to create a new formula to create your Master sheet calculations each time you have a new sheet for the end of the month, and there isn't a way to automate this since your end of the month sheets will be new additions each time.