1 2 Previous Next 17 Replies Latest reply on Jul 25, 2016 5:29 PM by priya.danda

    WOW Calculations

    priya.danda

      Hello All,

      I need help with wow calculations and I am using 9.2.

      From the above pic: Change is the wow (current week sales-prev week sales) and I used wow formula as-> sum([Gross Sales])-lookup(sum([Gross Sales]),-1)

      This formula is applying to all weeks but I want change to apply only for my last week(dynamic).So I created another calculation called Index-> LAST()=O and I applied Index to Filter and selected TRUE.

      I got solution like below picture.I got change (wow) for last week but I was unable to apply gross sales for all weeks.

       

      My question is how can i get Sales for all weeks and Change for my last week like below picture?

       

      Thanks,

      --Priya

        • 1. Re: WOW Calculations
          Stoyko Kostov

          Hi Priya,

           

          One possibility is to create 2 separate sheets - one that computes all sales, and the other - just the last week change - and combine them in a dashboard.

           

          Here's a post that you may find helpful - I think it addressed a similar scenario.

           

          % Change between two columns

           

          Let us know if you have more questions.

          1 of 1 people found this helpful
          • 2. Re: WOW Calculations
            priya.danda

            Thank you for the info, but Is there any way to get in one sheet both sales and change.

            Because I have around 12 sheets which need change calculations for each sheet and I should get all sheets in a single dashboard using sheet selector.

            Thanks,

            Priya

            • 3. Re: WOW Calculations
              Stoyko Kostov

              Hi Priya,

               

              If I understand you correctly, you would like to combine 2 tables in a sheet (sales and WOW), and have a filter act only on one of them (the WOW table).

               

              Typically this is not possible - the scope of the filter is the entire sheet - it can't be restricted to a table. That's why I proposed having the 2 tables in different sheets and combining them in a dashboard.

               

              Having many tables shouldn't be much of a difficulty - you would just have twice as many sheets - 2 for each table - and combine them all in a dashboard.

               

              For your particular scenario, since you need a single column from the WOW table, a custom aggregation may work: Aggregate Calculations

               

              Instead of a second table, you would have an aggregate calculation. That way you won't need a filter on it.

               

              Would you be able to provide a sample packaged workbook, and I'll try to figure out a solution?

               

              If aggregated calculations don't work, please describe what is concerning you for the WOW-on-separate-sheet solution, and I'll try to mitigate your concerns there.

              1 of 1 people found this helpful
              • 4. Re: WOW Calculations
                Joe Oppelt

                Priya -- It keeps coming back to this.  Questions like these are best handled with a sample workbook.

                 

                Let me suggest this though.  Don't put that INDEX calc on your filter.  Instead, incorporate it into your [Change] calc.

                 

                IF [Index] = WINDOW_MAX([Index]) then sum([Gross Sales])-lookup(sum([Gross Sales]),-1) else null END

                 

                Something like that.  It will do the lookup only when you are at the last week.

                1 of 1 people found this helpful
                • 5. Re: WOW Calculations
                  priya.danda

                  Please see the attached sample workbook.

                  I am trying to apply dashboard selector here, but when I am trying to drag change sheets into ALL dashboard, its not working well.(may be I am doing wrong in dashboard formating like horizontal,veritcal..).Please correct me if you know what wrong i am doing here.

                   

                  Thanks,

                  Priya

                  • 6. Re: WOW Calculations
                    Stoyko Kostov

                    Thanks Priya, but I can't open your workbook. It references a file that wasn't included.

                     

                    Could you supply a packaged workbook (.twbx)?

                    • 7. Re: WOW Calculations
                      priya.danda

                      Please see the attached packaged workbook.

                       

                      Thanks,

                      Priya

                      • 8. Re: WOW Calculations
                        Stoyko Kostov

                        Thanks! Now I'm able to see everything.

                         

                        The first suggestion I have is to select New Objects: Floating in the ALL dashboard. That way you can drag every sheet to any place you want, resize it any way you want, etc - gives you most freedom.

                         

                        Clear the sheets you've already inserted before choosing that (otherwise it would only apply to the new sheets you add to the dashboard).

                         

                        I'll look into your WOW calculations now and make further suggestions if I can.

                        • 9. Re: WOW Calculations
                          Stoyko Kostov

                          I wasn't able to get the change column for the last year only without using a new sheet. I always get all years with blanks. It's the same problem I had with the other question I answered (referenced above), and that's why I suggested using a separate sheet and a dashboard.

                           

                          If somebody else knows how to do it on the same sheet, please share. I would want to learn too.

                          • 10. Re: WOW Calculations
                            Joe Oppelt

                            In the Region sheet I modified the [Change] calc to function only on the last column.

                             

                            I'm with Stoyko, though.  I would consider using a separate sheet to display the single column of [Change].  You can format it a lot better as a separate sheet.

                            • 11. Re: WOW Calculations
                              Joe Oppelt

                              And now that I see this workbook, Priya, it looks like your goal is to move from dashboard to dashboard based on the parameter value the user selects.  (In your other thread.)

                               

                              I have never been successful at getting actions to move between dashboards based on the parameter chosen.

                               

                              I uploaded this sample workbook in your other thread, but I'll also add it here.  This is a simple way to move between dashboards.  See attached.

                               

                              I will say this though.  The way you have these sheets set up now, it lends itself very nicely to doing this all on one dashboard and do sheet swapping.  You already have the sheets set up to do that.

                              • 12. Re: WOW Calculations
                                priya.danda

                                Hello,

                                Thank you Joe, now I am able to get the result by using the formula given below.

                                 

                                IF [index]=window_max([index]) then

                                ((sum([Gross Sales]))-LOOKUP(SUM([Gross Sales]),-1))/ LOOKUP(SUM([Gross Sales]),-1)

                                end

                                 

                                Now, here is another situation:

                                By using above formula, I am able to get change percentage for latest week. Now, the user wants to see Change Percentage for latest Monday and Wednesday. Basically, I should be able to display change percentage for the latest Monday and Wednesday. I am not able to figure out how I can do it. I am thinking it should be similar to the above formula.

                                • 13. Re: WOW Calculations
                                  priya.danda

                                  I am posting a sample workbook, I need Current Monday (7/18/16) minus prev Monday (7/11/16).

                                   

                                  Thanks,

                                  Priya

                                  • 14. Re: WOW Calculations
                                    Joe Oppelt

                                    There might have been a more elegant way to do this, but what I've done will get the job done.


                                    See sample-Monday in the attached.

                                     

                                    I created a calc that gives is a numeric value for the day-of-week.  [weekday number]

                                     

                                    Take a close look in there.  If the day value is 1 (sunday) I added 7.  I did this because of how it will be used in the lookup calcs that will grab the Monday values.

                                     

                                    I created a calc to see what day of week is the last in the sheet.  This is important because I want to back up in the table to the Monday data.  So if the last day is Wednesday, I want to move back two days, for example.

                                     

                                    And then I created two calcs that do LOOKUP to grab the proper chunk of data.  In effect they go to the end of the table and back up as many as necessary to land on Monday.  (One moves back either 6 or less, and the other moves back between 13 and 7 depending on what the last day in the data is.

                                     

                                    For this sheet, all the table calcs just do TABLE(across).

                                     

                                    I have all the intermediate fields displayed on your sheet.  Of course, you don't need to display them.  They're just there for demonstration purposes.  And you don't really need the separate [Value of last/prior...] table calcs unless you need to display the actual separate values.  You can just combine them all in one giant calc, if you want.  (My preference for stuff like this is to compartmentalize the steps into separate calcs so that if someone else has to do debugging or modifying stuff later on it is easier to follow along.)

                                    1 2 Previous Next