-
Previous Month - Set Analysis
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 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
-
TestAdv_Campaigns_2.qvw 419.2 K
-
-
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 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 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 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 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 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)
-
Previous Month - Set Analysis
gdigiorno Sep 9, 2011 9:59 AM (in response to Stefan Wühl )Yes, weekend is nigh, nobody wants to think qv expressions
The expression you sent me:
=sum({<Day = {">=1<=$(=Day(today()))"}, Month= , Date =, Week=>} Payout)
give me total amount per month. I mean, month to date from september, and total payout from august. There's something missing and don´t know what.
Thanks,
-
Previous Month - Set Analysis
gdigiorno Sep 9, 2011 10:02 AM (in response to gdigiorno)Finally got it. Day formula was missing in the script.
Thanks for your help!
Regards,
-
-
-
-
-
-
-
-
-
-
-
Re: Previous Month - Set Analysis
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.
-
Re: Previous Month - Set Analysis
gdigiorno Sep 8, 2011 10:00 AM (in response to Marc Livingston)Hi Marcsliving,
I'm afraid your formula is not working properly. I'm newbie, so I can't find the error
Regards,
Guido
-
Re: Previous Month - Set Analysis
Marc Livingston Sep 8, 2011 10:03 AM (in response to gdigiorno)Sorry I should have tested it first.
try this:
sum(if(Date>=weekstart(date(today()-21)),sales))
-
-
-
-