topic Re: Previous Month - Set Analysis in QlikView

Previous Month - Set Analysis

Wed, 07 Sep 2011 21:40:48 GMT

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 — Wed, 07 Sep 2011 22:15:53 GMT

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 — Wed, 07 Sep 2011 22:32:51 GMT

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

Thu, 08 Sep 2011 12:33:20 GMT

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 — Thu, 08 Sep 2011 12:48:59 GMT

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

Thu, 08 Sep 2011 12:56:14 GMT

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

Thu, 08 Sep 2011 13:28:49 GMT

For example: Sales by Week:

$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!

Re: Previous Month - Set Analysis

Thu, 08 Sep 2011 14:00:34 GMT

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

Thu, 08 Sep 2011 14:03:16 GMT

Sorry I should have tested it first.

try this:

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

Previous Month - Set Analysis

swuehl — Thu, 08 Sep 2011 14:05:23 GMT

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

Thu, 08 Sep 2011 21:10:36 GMT

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 — Thu, 08 Sep 2011 23:56:32 GMT

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

Fri, 09 Sep 2011 12:35:30 GMT

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 — Fri, 09 Sep 2011 12:43:22 GMT

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

Fri, 09 Sep 2011 12:59:18 GMT

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 — Fri, 09 Sep 2011 13:08:23 GMT

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

Fri, 09 Sep 2011 13:59:52 GMT

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

Fri, 09 Sep 2011 14:02:28 GMT

Finally got it. Day formula was missing in the script.

Thanks for your help!

Regards,