8 Replies Latest reply on Jul 27, 2015 7:51 AM by Adam Karolewski

    Running Sum Across Pages

    John Mogielnicki

      Hello,

       

      I'm stumped on this one.  Is there any way to create a running sum that will subsist across pages?  See the attached packaged workbook.    I'm trying to make it so that the bottom chart will be a running sum, but because I have my dates in the pages shelf I can't seem to get this to happen.  Even using window_sum doesn't seem to work.  Any ideas?

       

      Thanks!

       

      John

        • 1. Re: Running Sum Across Pages
          John Mogielnicki

          Dang, I thought this was a decent question, but no replies.  I've searched the forums, but haven't been able to find a solution anywhere.  Any ideasJonathan Drummey Shawn Wallwork Alex Kerin?  You guys have all been super helpful in the past when I've gotten stuck (for which I an incredibly grateful).

           

          Thank you.

           

          John

          • 2. Re: Running Sum Across Pages
            Jonathan Drummey

            Hi John,

             

            Not everybody is on the forums all the time.

             

            My understanding is that you want the Window Running Sum to persist no matter what the value on the Pages Shelf is. I don't know of any way to make the Pages Shelf only affect one part of a view and not another, so the solution I'd suggest is to use two worksheets in a vertical layout container on a dashboard. One has the Pages Shelf on, the other doesn't. There's probably another way to do this using a cross product, but the dashboard solution would be a lot easier.

             

            Jonathan

            • 3. Re: Re: Running Sum Across Pages
              Shawn Wallwork

              Oh you silver-tongued devil! I can get you dots with a dual axis, but I can't remember the trick to making those into lines (see attached). Jonathan will probably know.

               

              EDIT: I totally misunderstood the assignment. My mind must already be on vacation.

              • 4. Re: Running Sum Across Pages
                Alex Kerin

                Since John asked so nicely.. Another option would be to replicate the pages with a parameter and use a date calc and the special lookup([date filter],0) to allow access to the other values.

                • 5. Re: Re: Running Sum Across Pages
                  John Mogielnicki

                  You guys are the best!  Thank you so much for the quick responses, now I know how to get your attention!

                   

                  Now I feel bad, the basic solution I was looking for was simple, I feel like I should have been able to figure it out.  But thank you Shawn and Jonathan, I'm sure this will be helpful for others.

                   

                  As for the solution that allows for a line, thank you Alex, I'm going to experiment with using a parameter in lieu of the pages shelf and see if that works.  The only problem with that (I think) is that there is no "play" button on parameters, so you would have to click through each parameter value.  The play feature is important to the actual project I'm working on.

                   

                  Thanks again for the responses!

                   

                  John

                  • 6. Re: Running Sum Across Pages
                    Chad Kovac

                    I came to find out the same thing... it's a shame that we have to find all of these hacks/work-arounds to do things that should be features of the software we've spent so much money for.

                    • 7. Re: Running Sum Across Pages
                      Alex Kerin

                      Yes and no - the pages functionality is designed for a specific use that may be broken if this unusual use case was accounted for. I don't regard using parameters instead as a hack - it's just using the functionality that's there.

                      • 8. Re: Running Sum Across Pages
                        Adam Karolewski

                         

                        Hi, I know much time has passed but I was looking today for the solution for the same issue and I made it. It was possible thanks to LOD (level of details) expressions introduced in TB 9.0.

                         

                        I wanted to count customers by their first order and show it in time how the number of customers were cumulating using Pages. So I wanted to use Date of first order [Date] on Page and minimum of that date for each customer and count it. So:

                         

                        1. On Page I put [Date]

                         

                        2. On column I put {fixed [Cust ID]:min([Date])}

                         

                        3. I created calculated field and I put it on rows:
                        IF MIN({FIXED [Cust ID]:MIN([Date])})<=ATTR([Date]) THEN

                         

                         

                        SUM({EXCLUDE [Date]:COUNTD([Cust ID])})

                         

                         

                        END

                         

                         

                        4. Running sum on that calculated field

                         

                         

                        And it worked!

                        Please experiment on your own with this.

                         

                         

                        Adam Karolewski