Previous Month - Set Analysis

[#1]

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

[swuehl · Accepted solution]

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

[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

[#4]

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

[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

[#6]

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.

[#7]

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!

[#8]

Hi Marcsliving,

I'm afraid your formula is not working properly. I'm newbie, so I can't find the error

Regards,

Guido

[#9]

Sorry I should have tested it first.

try this:

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

[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?