7 Replies Latest reply: Sep 14, 2011 7:45 AM by Chris Cammers

# Previous MTD

I have been tasked with building a dashboard and it has nearly been the death of me (yes, slightly dramatic).  I am a beginner with no programming experience, so i am setting up this dashboard pulling nearly 10 or 12 files into it and trying compare the current month with the previous month to date.  I want the figures from Sept 1-9 and Aug 1-9.  I have attached a document with one chart listed on it.  I am also fairly inexperienced with variables as well.   I think if i can just get help to start, I can pick the rest up.  I do know the scripting probably looks pretty bad as well, so if i can get any assistance with a master calendar or anything, i would greatly appreciate it.

Thank you,

Kristy

• ###### Re: Previous MTD

My sympathies, you have been given an unfair task, I hope your boss has reasonable expectations.

Your QVW is pretty big and I have not been able to download it yet, browser keeps stopping. but I can help with your questions without knowing your data.

First Here is some code for a master calendar, I am keying on a field called order date but you can use whatver works from your data.

//Establish the Range of Dates in your data
DateRange:
Min([Order Order Date]) as MinDate,
Max([Order Order Date]) as MaxDate
Resident

//Assign the dates to some variables that we'll use to caclulate the calendar
Let vToday = Peek('MaxDate', 0, 'DateRange');
Let vDataStart = Peek('MinDate', 0, 'DateRange');

//this table gives us all the dates and saves us a bunch of code by being able to
//refer to simply TempDate in the next step
TempCalendar:
\$(vDataStart) + RowNo() - 1 as Num,
Date(\$(vDataStart) + RowNo() - 1) as TempDate
Autogenerate
num(\$(vToday)) - num(\$(vDataStart)) + 1;

// This is the master Calendar, it is a very simple calendar but if you
// different time dimensions
MasterCalendar:
TempDate as [Order Order Date],
Month(TempDate) as CalendarMonth,
MonthStart(TempDate) as MonthStart,
MonthEnd(TempDate) as MonthEnd,
Year(TempDate) as Year,
'Q' & ceil(Month(TempDate)/3) as FiscalQuarter
resident TempCalendar;

//Clean up after yourself
Drop Table TempCalendar;
Drop Table DateRange;

I'll post another reply with the MTD and PMTD syntax.

• ###### Re: Previous MTD

Here are some expressions that will sum a field for the current month to date, based on the user's selections and the previous month to date. It uses set analysis which can be pretty overwhelming for a newbie but I hope this helps

This one does the most recent available month to date

Sum({\$<[Order Order Date]={"\$(='>=' & MonthStart(Max([Order Order Date])) & '<=' & Max([Order Order Date]))"}>}SubTotal)

This one does the previous MTD

Sum({\$<[Order Order Date]={"\$(='>=' & MonthStart(Max([Order Order Date]),-1) & '<=' & AddMonths(Max([Order Order Date]),-1))"}>}SubTotal)

The important thing to keep in mind is that this one is sensitive to user selection so it is important that you call it out in the column headings or something so the user is aware of what they are seeing.

Here is an expression to show the range of dates for the current month
='Current Month (Order Date >=' & MonthStart(Max([Order Order Date])) & '<=' & Max([Order Order Date]) & ')'

Here is one for the previous month

='Previous Month (Order Date >=' & MonthStart(Max([Order Order Date]),-1) & '<=' & AddMonths(Max([Order Order Date]),-1) & ')'

For other users who may read this later, you can manipulate the set expressions using different date functions to achieve various date ranges like YTD and QTD and so on.

good luck!

Happy Qliking

Chris

• ###### Re: Previous MTD

I forgot one thing,

You have to override the selections on all the fields on the calendar table, the following expressions will do that.

Current Month To Date
Sum({\$<[\$(=concat({1<\$Table={'MasterCalendar'}>} \$Field,']=,['))]=,[Order Order Date]={"\$(='>=' & MonthStart(Max([Order Order Date])) & '<=' & Max([Order Order Date]))"}>}SubTotal)

Previous Month To Date
Sum({\$<[\$(=concat({1<\$Table={'MasterCalendar'}>} \$Field,']=,['))]=,[Order Order Date]={"\$(='>=' & MonthStart(Max([Order Order Date]),-1) & '<=' & AddMonths(Max([Order Order Date]),-1))"}>}SubTotal)

• ###### Previous MTD

Thank you so much!  I appreciate your help.  I have one more thing that needs to be addressed.  I did find this solution online, but have since lost it.  I have a few fields that are accumulating data, but i dont want them too.  For example:

Jan 500

Feb1000

March 1500

April 2000

Since each month is adding 500, i just want the 500.  They are accumulating daily.  Do you know of an expression that will pick up yesterdays revenue.

Thankx!

• ###### Previous MTD

Period over period analysis can be tough for beginners. You may want to start with a prebuilt component such as can be found here:

http://iqlik.wordpress.com/2011/01/01/point-in-time-reporting-out-of-the-box/

-Rob

http://robwunderlich.com

• ###### Re: Previous MTD

Very Nice!

I agree Period over Period analysis is a tough topic for newbies.

For me this is always a point of tension between the Qlikview developer and the business, raising the "why can't you just select what you want?" issue.

• ###### Re: Previous MTD

Check your accumulation options on the expressions tab of the chart properties(lower left). Keep in mind these settings are for each expression so you have to select the expression to see how it is set.