17 Replies Latest reply: Sep 9, 2011 10:02 AM by gdigiorno RSS

    Previous Month - Set Analysis

    gdigiorno Sep 7, 2011 5:40 PM

      Hi,

      I'm having some problems trying to show only last month results.

      I have Aug 2011 and Sep 2011 information. What I need to show is only Aug 2011 results, though my selection is all dates (Aug 2011 and Sep 2011).

       

      I want to show by country, Aug payout (pivot table called "august payout". I tried several formulas, but in all cases I get the total payout considering the two months.

       

      Also, I'm interested in how doing the same by week (e.i: week 15 Aug compared to Week 18 Aug). Not with before formula.

      And finally, is there any way to show only last 3 weeks in a pivot table. I have more all weeks selected, but I want to show only last 3.

       

      Thanks for your help.

      Regards,

      Guido

       

      Attached is the .qvw

        • Previous Month - Set Analysis
          Stefan Wühl
          Stefan Wühl Sep 7, 2011 6:15 PM (in response to gdigiorno)

          Hi Guido,

           

          you could use this for previous month's  Payout figures:

          =sum({<Date = {">=$(=monthstart(addmonths(today(),-1)))<$(=monthstart(today()))"}, Month=, Week=>} Payout)

           

          I'll have a look to your other questions, too, what do you mean with:

          "not with before formula"? (Ah- the inter record function?)

           

          Regards,

          Stefan

            • Re: Previous Month - Set Analysis
              Stefan Wühl
              Stefan Wühl Sep 7, 2011 6:32 PM (in response to Stefan Wühl )

              Hi Guido,

               

              maybe attached application helps you. I added some columns with fixed August / week data and the pivot table (last 21 days).

               

              Regards,

              Stefan

              • Re: Previous Month - Set Analysis
                gdigiorno Sep 8, 2011 8:33 AM (in response to Stefan Wühl )

                Hi Stefan,

                Thanks a lot for your help. The formula you provided worked OK. I have QV Personal Edition, so I can't open other's files.

                Regarding to showing the same by week, I would like to know how can I create a pivot table, using "Week" as a Dimension, but showing the last 3 weeks (I think I have to edit the dimension). I.e.: I have the following weeks: August01 - August08 - August15 - August22 - August29 - September05. I want to show only data for last 3 weeks, but not filtering the last 3 weeks, I want all data avaiable.

                 

                Thanks!

                Regards,

                Guido

                  • Re: Previous Month - Set Analysis
                    Stefan Wühl
                    Stefan Wühl Sep 8, 2011 8:48 AM (in response to gdigiorno)

                    Hi Guido,

                     

                    I am going to post the expressions also as plain text later on, but what do you mean with:

                    "I want to show only data for last 3 weeks, but not filtering the last 3 weeks, I want all data avaiable."

                     

                    I don't understand this, sorry.

                     

                    Stefan

                      • Re: Previous Month - Set Analysis
                        gdigiorno Sep 8, 2011 9:28 AM (in response to Stefan Wühl )

                        For example: Sales by Week:

                         

                        Week1 | Week2 | Week3 | Week4 | Week5 | Total

                        $1000  |   $1200 |  $800   |  $1100 |  $1500  | $5600

                         

                        I would need to show:

                         

                        Week3 | Week4 | Week5 | Total

                        $800   |  $1100 |  $1500  | $5600

                         

                        Total amount generated, but only showing the last 3 weeks. Is it clear now?

                        Thanks!

                          • Previous Month - Set Analysis
                            Stefan Wühl
                            Stefan Wühl Sep 8, 2011 10:05 AM (in response to gdigiorno)

                            Yes, I think it is.

                             

                            For the 3 weeks table do this:

                            Create a straight table chart, with week as dimension and partial sums enabled.

                            Then, as expression use something like:

                             

                            =if(Dimensionality()=0,

                            sum({<Date=,Week=,Month=>} Payout),

                            sum({<Date = {">=$(=weekstart(today()-14))"},Week=,Month= >}Payout))

                             

                            which calculates either the total sum or the last 3 weeks sum depending on dimensionality (total row or week dimension).

                             

                             

                            For a fixed week comparison similar to above year column, you could try something like

                            =sum({<Week = {'August 15'}, Month=, Date=>} Payout)

                             

                            Hope this helps,

                            Stefan

                             

                            P.S: I used weekstart(today()-14) in above expression, because I think that will give you the last 3 weeks including the (not full) current week, right?

                              • Re: Previous Month - Set Analysis
                                gdigiorno Sep 8, 2011 5:10 PM (in response to Stefan Wühl )

                                Stefan, the formula worked OK!

                                One more question: How can I compare months (August vs September) considering only the same days? I.e.Sum of Sales in the first 5 days of each month.

                                 

                                Thanks again,

                                Regards,

                                  • Previous Month - Set Analysis
                                    Stefan Wühl
                                    Stefan Wühl Sep 8, 2011 7:56 PM (in response to gdigiorno)

                                    I think you could achieve this if you add

                                    Day(Date) as Day,

                                     

                                    to your data model, then just add the Day to you filter:

                                     

                                    =sum({<Day = {">=1<=5"}, Month= {'Aug 2011'}, Date =, Week=>} Payout)

                                     

                                    (sometimes the assignments are a bit tricky, the format of the field must match exactly the format of the element set. Most often this may be a problem with date formatting).

                                     

                                    And please remember when adding (date) fields the user can select on, that you may need to clear it in your set expression.

                                     

                                    Regards,

                                    Stefan

                                      • Previous Month - Set Analysis
                                        gdigiorno Sep 9, 2011 8:35 AM (in response to Stefan Wühl )

                                        Hi Stefan,

                                        Thanks for your response.

                                        The problem I found here, is that is not updated automatically. I mean, my idea was to have a formula, comparing month to date from current month, against same days from month before.

                                         

                                        i.e.: Payout of first 9 days in September vs. Payout of first 9 days in August.

                                         

                                        This should be updated when I reload the script when new data. Month to date of current month compared with the same period of time of the month before. Does this makes sense?

                                         

                                        Regards,

                                        Guido

                                          • Re: Previous Month - Set Analysis
                                            Stefan Wühl
                                            Stefan Wühl Sep 9, 2011 8:45 AM (in response to gdigiorno)

                                            Ah, I see current month till today, compared to the equivalent period last month?

                                             

                                            try (removing the Day again):

                                             

                                            =sum({<Date = {">=$(=monthstart(addmonths(today(),-1)))<=$(=addmonths(today(),-1))"}, Month= ,  Week=>} Payout)

                                             

                                            Stefan

                                             

                                            edit: Month and Week fields cleared, removed second Date in set expression

                                             

                                             

                                              • Previous Month - Set Analysis
                                                gdigiorno Sep 9, 2011 8:59 AM (in response to Stefan Wühl )

                                                Do you mean to remove "bold" expression?

                                                 

                                                =sum({<Date = {">=$(=monthstart(addmonths(today(),-1)))<=$(=addmonths(today(),-1))"}, Month= ,  Week=>} Payout)

                                                 

                                                If I use "Month" as a dimension, only August's Payout appear. I need something like this:

                                                 

                                                August 2011 | $10.000

                                                September 2011 | 15.000

                                                 

                                                Both Payout values are taken from month to date from september compared to the same period of time with August.

                                                 

                                                Thanks and sorry if I misunderstood something.

                                                 

                                                Regards,

                                                Guido

                                                  • Re: Previous Month - Set Analysis
                                                    Stefan Wühl
                                                    Stefan Wühl Sep 9, 2011 9:11 AM (in response to gdigiorno)

                                                    My last expression will calculate only for the previous months, right.

                                                     

                                                    If you want it for all months, then maybe add Day field again and use:

                                                     

                                                    =sum({<Day = {">=1<=$(=today())"}, Month= , Date =, Week=>} Payout)

                                                     

                                                    This should select and calculate Payout only for the days to date, for all months (I hope, haven't tested).

                                                     

                                                    Sorry If I keep misunderstanding you, weekend is nigh.

                                                     

                                                    edit: Getting hard now..

                                                    =sum({<Day = {">=1<=$(=Day(today()))"}, Month= , Date =, Week=>} Payout)

                                                     

                                                     

                                    • Re: Previous Month - Set Analysis
                                      Marc Livingston
                                      Marc Livingston Sep 8, 2011 8:56 AM (in response to gdigiorno)

                                      you could probably use the expression:

                                      sum(if(Date>=weekstart(date((today()-21)),sumfield))

                                       

                                      Which should only return the summary of all info that has a date greater than the start of 3 weeks ago.