Will you ALWAYS have data on the last date of any particular month? (Suppose the last day of some month has no transactions.)
How do you know when a month is actually finalized?
Is it safe to say that if you have a row in the monthly sheet, then the month is finalized?
Joe -- thanks for replying.
For the monthly data, yes, if there's a row for the date, then it means that the month has been finalized and the value has been verified.
In terms of daily numbers, it doesn't always fall on the last day of the month. For the sake of the sample data, I included a value for each day, but if the last day of a month is a holiday or weekend, there would not be a row for it.
Also, the way it works is the monthly data is always finalized a couple days after the month end, so if there's a row for July 2016, it can be assumed that all the daily values for July have been inputted into the database.
9.0 workbook attached.
One thing I might be doing wrong is computing the average. I'm just taking the average of the averages, but I guess I should probably be taking the sum of the individual daily values and divide by the number of rows.
But this is the approach you can use if you are going to do this in separate data sources.
Sheet 1 just shows how to join the individual data sources to get the daily averages.
Sheet 2 shows how to create your calcs to do what you want with the daily averages.
Sheet 3 shows how to get a single column of numbers for daily averages.
Sheet 4 shows the same thing for the monthly values. (I can't see a way to put both on the same sheet because there isn't a dimension to use to make different columns.)
Dashboard shows sheets 3 and 4 combines for one visualization.
If this is getting you in the right direction, we can work on adjusting the calcs for the daily averages if I wasn't doing the right arithmetic there.
Hide month 2.twbx 31.9 KB
Joe, thanks for taking the time to help out with this -- I really appreciate it!
I'm less worries about the arithmetic, as in my real data, I've actually gotten the math to work out (it's slightly more complicated than just getting the AVG). My biggest issue has been the whole logic behind only using daily dates that are earlier than the last approved monthly date.
I reviewed your workbook and the solution is great, but the only issue is that in my real data, there's actually another layer of complexity in that we could have multiple entries for a single date (because my data has different lines of businesses) For example, Group A, Group B, Group C, Group D, would all have an entry for June 3rd, 2016 -- which is why using the ATTR() function isn't working because it will come out as null since there are multiple entries with the same date.
Any advice on how to overcome this?
Please see the updated attached. For simplicity's sake, I divided the data into two groups "A" and "B". There are now duplicate date entries for a few days were both A and B have a value.
What I want to show now is (for Dailies):
Last Month Avg for A
Last Month Avg for B
Last 2 Month Avg for A
Last 2 Month Avg for B
Last 3 Month Avg for A
Last 3 Month Avg for B
Same logic as before -- do not include any daily data for days where the month has yet to be approved (aka no entry in the Monthly)
Date Test Data v2.xlsx 14.7 KB
And what do you want to do for the MONTHLY averages? Still a single number as it is in the MONTHLY sheet? If you had A and B categories in the MONTHLY sheet, you could join on that and break it apart easily. If you did NOT want to do that, then I can do stuff in the secondary sheet, but it gets messier.
Hi Joe, for monthly, yes, still the single number. Let's say that value is the same for both groups.
See attached. I just modified Sheets 2 and 3 to show what to do with the secondary data source.
Actually, I would consider reshaping the data in the daily data to have multiple values (by category) in each row. The calcs I made in the daily data source just simulates that. But if you have 100 different category values, it could get really messy.
Hide month 4.twbx 38.4 KB
Know what? I just realized something. I'm going to play with this making the daily data the primary source. Please hold...
See Sheet 5 in the attached.
Daily is now the primary source.
Edit the filter for MY(Date). (Notice that the filter is in the secondary source.)
I excluded NULL. That means that if the row for July is not there, the July daily data will not show. (Take the filter off to see what I mean. back-arrow will put it back on.)
Now all the principles I applied in calc-ing the last month, total-two-months, and total-three-months can be applied here. You'll have to rewrite the calcs to look at the sums and averages in the primary instead of the secondary source, but the way you'll do it will be similar. You just won't need [Value A] and [Value B] any more. And no matter how many groups you have, it will automatically do it for you because Daily (where all the moving parts are) will be the primary source.
Hide month 5.twbx 43.1 KB