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

    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
          Chris Cammers

          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.

            • Re: Previous MTD
              Chris Cammers

              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
                  Chris Cammers

                  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
                  Rob Wunderlich

                  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
                      Chris Cammers

                      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
                      Chris Cammers

                      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.