Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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.
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!
Hi Marcsliving,
I'm afraid your formula is not working properly. I'm newbie, so I can't find the error
Regards,
Guido
Sorry I should have tested it first.
try this:
sum(if(Date>=weekstart(date(today()-21)),sales))
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?