6 Replies Latest reply on Feb 21, 2018 9:44 PM by Diogo Braga

    Show marks label on 2 specific date points in a line

    Diogo Braga

      This viz is comparing application volume between 2 academic years.

      Blue represents 2017-2018 and orange 2018-2019.

       

      I need a marks label on 2 points on the blue line.

       

      Week 39 - representing the running total of applications generated up to Week 39 on 2017-2018

      Current Week - 2018-2019 will always be showing the current year's week number, so we need to display the same week number for 2017-2018 for comparison.

      2018-02-21_1236.png

       

      Disclaimer: I asked this before, but the data source and calculations in this workbook are different and for some reason, I am being able to adapt it.

        • 1. Re: Show marks label on 2 specific date points in a line
          John Sobczak

          I'm pretty sure this is doable and without spending time to figure out the calcs, the method would be to dual axis this by duplicating your measure on the rows shelf, and then synchronizing the axis.  Then through table calc or maybe LOD figure out a way to stop the 2017-2018 line where the 2018-2019 line stops.  Then label the line ends on this axis.

          • 2. Re: Show marks label on 2 specific date points in a line
            Diogo Braga

            Thanks for the reply.

             

            The previous approach was dual axis and the marks label I am asking here was working, but other things were not, so we changed it to one axis after we created datetrunc calculations to address the challenges here: Running total line showing comparison between 'week year' with 'week year -1' with Shinichiro Murakami's help.

             

            I am trying to get back to the dual axis method using the datetrunc calculations, but it's not 100% yet.

             

            ________________________________

            Week 39 label is working:

             

            Week 39:

            IF MIN(DATEPART('week', [Apptime]))=39 THEN [Application 2017-2018] END

             

            However, if the school does not have data for Week 39 then it will show Null, so the calculation needs to be tweaked so it shows the total of the latest week with data.

             

            ________________________________

            Current week calculation is not working. For some reason it's showing on W53, and the total is not correct. School X week 8 (current week) should be 599 for school x's total for Week 8 (current week).

            This is the calcuation:

             

            Current Week:

            if min([Week])=min([Newest Week] )

            then window_sum(COUNTD(if [Academic Year]='2017-2018' then [Unique ID]end),first(),0) end

             

            Newest Week:

            ({fixed :max(if not isnull([Unique ID]) and [Academic Year]="2018-2019" then datepart('week',[Apptime]) end)})

             

            Refer to:

            Sheet 4: one axis method

            Sheet 4 (2): dual axis method

             

            Thank you very much!

            • 3. Re: Show marks label on 2 specific date points in a line
              Shinichiro Murakami

              Some thing like this?

               

               

               

               

              Regards,

              Shin

              1 of 1 people found this helpful
              • 4. Re: Show marks label on 2 specific date points in a line
                Diogo Braga

                Thank you very much! I truly appreciate your help on this. It's close, but not quite there yet.

                 

                Here are some issues that I found and can't identify what's causing it.

                 

                The 2017-2018 running total distinct count of 'unique id' for school X is different in the following scenarios:

                 

                599 here on this table created to check data:

                Screen Shot 2018-02-21 at 10.17.46 PM.png

                 

                625 here when we filter academic year:

                2018-02-21_2307.png

                 

                630 here when we apply academic year to the color mark:

                2018-02-21_2309.png

                 

                Any thoughts?

                 

                We also need a schoolwide total, but when I remove the 'Grade' pill, this happens:

                2018-02-21_2227.png

                 

                This happens because not every grade has applications for every week. In this case, some grades don't have applications for Week 8 and Week 39, so it shows the total for the most recent week with applications. This is an issue that I'd like to avoid if you can help me think of a solution. Let's say in Week 7 the total application is 20, but if Week 8 has no application it means the total application still 20. Anything we can do to carry over the result from the last week with application total to weeks without applications?

                2018-02-21_2233.png

                • 5. Re: Show marks label on 2 specific date points in a line
                  Shinichiro Murakami

                  HI Diogo,

                   

                  I understand the issue, and something is do*able, but just beyond my reasonable resource consumption level.

                  It's beyond the forum supportable level already.

                  I don't stop if someone might help, but I cannot spend so much time on single question.

                   

                  Thanks,

                  Shin

                  • 6. Re: Show marks label on 2 specific date points in a line
                    Diogo Braga

                    Yes, I understand. I am really grateful for all your help! You already went above and beyond.

                     

                    I am open if you want to share some ideas on how I should move forward to solve this problem. Given the current issues, do you think one axis or two axis is better? Could Index () solve for data not showing for every week? The example if Week 7 running total app is 20 then show the same 20 for Week 8 even if no app was generated for that week.

                     

                    I appreciate any solution key words to guide my research! Thank you!