Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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:
Load
Min([Order Order Date]) as MinDate,
Max([Order Order Date]) as MaxDate
Resident
SalesOrderHeader;
//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:
Load
$(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
//search the date functions in help you'll be able to add a wide range of
// different time dimensions
MasterCalendar:
Load
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.
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
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)
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
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.
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!
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.