Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

17 Replies
Not applicable
Author

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,

swuehl
MVP
MVP

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

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

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

swuehl
MVP
MVP

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)

Not applicable
Author

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,

Not applicable
Author

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

Thanks for your help!

Regards,