4 Replies Latest reply on Jan 7, 2019 7:07 AM by Khoma Usire

    Which week is this?

    Khoma Usire

      Hello, good people.


      Imagine this: you've got subjects randomly investing throughout the year. My viz shows that someone's invested for 8 consecutive weeks.

      I'm loving the fact that I can put the total amount of weeks into the tooltip with {FIXED [Investor] : COUNTD([Weeks])},  but I'd also like the tooltip to show how many weeks he's been investing prior to that particular point I'm hovering my mouse over.

      So when I'm hovering over the first week when that investor was active, the tooltip would say '1' and at the last point it would say '8'. None of the count or index functions have seen to do me any good. Can anyone help?


      Also apologies for not posting a workbook, but I've starved myself severly trying to figure this out and I can't spend more time anonymising data in two major data sources.



      Thank you for whoever gives this a try,


      All best



        • 1. Re: Which week is this?
          Joe Oppelt

          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

            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.


            Tab help.PNG



            Thank you!

            • 3. Re: Which week is this?
              Joe Oppelt

              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.

              1 of 1 people found this helpful
              • 4. Re: Which week is this?
                Khoma Usire

                Thank you Joe, this is great!


                Opens up options that I didn't knew existed