-
1. Re: Which week is this?
Joe OppeltJan 3, 2019 11:29 AM (in response to Khoma Usire)
You're probably going to need a sample workbook, as hard as that might be for you.
It's tough to pick up something like this and hack up an example on my own that comes close to your application. Far too often I'll spend time doing that, only to get a reply that it's not like what you have, so it doesn't help...
If you need to anonymize your stuff, follow the steps demonstrated in the video linked here:
Video demonstrates how to anonymize your workbook/data
I did an example on the fly in the video, and it took about 10 minutes.
I don't need all your investors. 3 or 4 guys whose history embody some typical examples will do. And I don't need hundreds of weeks of history. Just enough to show some consecutive weeks, and various start dates, etc.
But let me also suggest that you consider a calc like this:
{FIXED [Investor] : MIN([Weeks])}
(or MIN([Investment Date]) or something like that.)
You'll have the investor's start date, and you can have a calc from mark to mark that does a difference from the start to the current week/date.
If you need to keep track of CONSECUTIVE weeks (and if there can be breaks in the middle of the investor's history) then you'll need to run calcs that use PREVIOUS_VALUE() to see what was in the previous week and keep track of consecutive stretches. And if I'm going to help with that, I'm going to need a sample workbook.
-
2. Re: Which week is this?
Khoma Usire Jan 4, 2019 1:01 AM (in response to Joe Oppelt)Hi Joe!
Here's the workbook! Everything should be in at least approximately English language and anonymized.
Below is the variable that I want to see progress in. Currently it only shows how many weeks in total the investments (or in this case TV Reach) have a value in. What I'd like it to do is show how many of those total weeks are already past the point I'm hovering over. Please keep in mind that they all start and end differently.
Thank you!
-
Tracking Help II.twbx 85.9 KB
-
-
3. Re: Which week is this?
Joe OppeltJan 4, 2019 8:36 AM (in response to Khoma Usire)
In the attached I made a new sheet just to get actual numbers for easier visual analysis for now.
So I filtered to the same guys you had pulled on your KPI sheet. And I just made a simple crosstab. I'm using SUM([TV Reach]) here, but you can equally use your [Variable Selector] field the same way.
I made a calc to tell me the first week for any investor. You'll see that the value remains the same across the whole row (as it should.) As it turns out I never used that, but it's still displayed here, and it may cone in handy for you elsewhere.
Because there are gaps in weeks, I can't just say "subtract the start week from the current week" and know how many weeks of investing the guy has done. So I made a different calc that increments when there is actual data in any week. See [Investment weeks so far]. You can see the results on the sheet.
Now, for investor 2, when you are on week 14, it is only his 10th week of investing. And you already know he has 31 weeks on the books, so he still has 21 in the future.
Special note: My running-sum calc will increment if there is a data value of zero. That's not a null, and my calc is only testing for null conditions. If you want to skip counting a week with a zero value, you can add "OR" logic in there to skip a zero value as well.
Special note #2. My [Investment so far] calc is a table calc. Right now on this sheet the setting for it is the default : TABLE(across). I'm pretty sure the same will hold on your KPI sheet, but elsewhere you may need to specify addressing of dimensions depending on how your sheet is designed. If you run into that, ping back and I can take a look.
-
Tracking Help II_v10.5.twbx 99.1 KB
-
-
4. Re: Which week is this?
Khoma Usire Jan 7, 2019 7:07 AM (in response to Joe Oppelt)Thank you Joe, this is great!
Opens up options that I didn't knew existed