Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a pivot table which holds a total amount of hours by area. Each day the data reloads and shows the most up to date data in the pivot.
I want to show the change since yesterday, but as I have reloaded I have lost yesterdays data due to the reload.
Is there any way I can create a column in the table that keeps yesterdays totals.
A bit like the example below .....
Refreshed data held in column A
Auto Copy and paste value (Column A) into new column B before reload
reload data
Refreshed value in Column A / Yesterdays value in Column B
It might be a macro, but I have no idea.
Any help appreciated?
Thanks
Phil
So, ColumnB is your Yesterday's Data Only???
or
It merges all the the previous data into Column B after reload??
Create a new column with today's date in it :
Today() as [Date Loaded]
The instead of overwriting the old qvd, read into a resident table and then concatenate the new data onto it.
The you will have all the data with a field telling you when each version of it was loaded.
Bill
I see what you are saying, that might work, but the data load is massive already, so doubling the data seems a bit extreme when I just want to copy one column in a summary pivot table and I fear I would start to get data messages.
Is there another way?
Regards
Balraj
Yes, column B is yesterdays data only, but as the script has been refreshed, the source data is not available to interigate.
Regards
Phil
HI,
How is your dashboard set up?
Do you store your data out to qvd's?
If so, change your script, first load your qvds alias the fields as needed, then load the new data as normal and join to the older data, store back out to qvd ready to repeat the next day.
Doing this you can either just keep the last day or any number of days.
Mark
How about an extra qvd with just :