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

Previous Month - Set Analysis

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
    swuehl

    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
      swuehl

      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

      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
        swuehl

        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

          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
            swuehl

            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

              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
                swuehl

                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

                  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
                    swuehl

                    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

                      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
                        swuehl

                        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

        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.