Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

Previous Month - Set Analysis

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

17 Replies
MVP
MVP

Previous Month - Set Analysis

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

MVP
MVP

Re: Previous Month - Set Analysis

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

Not applicable

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

MVP
MVP

Re: Previous Month - Set Analysis

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

Not applicable

Re: Previous Month - Set Analysis

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.

Not applicable

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!

Not applicable

Re: Previous Month - Set Analysis

Hi Marcsliving,

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

Regards,

Guido

Not applicable

Re: Previous Month - Set Analysis

Sorry I should have tested it first.

try this:

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

MVP
MVP

Previous Month - Set Analysis

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?

Community Browser