Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Alex,
Here is my approach.
Most important thing to understand is how do you want your end users to access the report. There are two ways...
You can calculate Max Year, Max Quarter and Max Month for the whole document. This way users will always view the latest data. I mean I would use {1} instead {$} where {1} represents full dataset and {$} for current selection.
Now, I am using the first approach to always show current period and previous period.
Step 1: Declaring Variables
I would declare my variable to calculate the CurrentYear, CurrentQTR, CurrentMonth & CurrentWeek. Something like this...
vCurrentQTR = Max({1} QuarterID)
vPreviousQTR = Max({1} QuarterID) -1
Same will be case for all the time dimensions.
*Please note that you can declare these variable in Load Script for better performance instead of declaring within QV document.
Step 2: Expression Building
Now, I would use these variable to create expressions. For example if I wanted to calculate Sales for CurrentQTR & PreviousQTR. I would use the below expressions...
Sales for Current QTR : Sum({$<QuarterID={$(=($(vCurrentQTR)))}>} Sales)
Sales for Previous QTR : Sum({$<QuarterID={$(=($(vPreviousQTR)))}>} Sales)
Again, You can just replace your variable for other time dimensions with same expression.
Let's Assume If you wanted to QTR over QTR Sales then I would use...
Sum({$<QuarterID={$(=($(vCurrentQTR)))}>} Sales) / Sum({$<QuarterID={$(=($(vPreviousQTR)))}>} Sales)
_________________________________________________________________________________________________________________________________
Let's assume that you wanted to give the power to User to select Time Dimensions. I mean if you wanted to calculate the above expression for current selection then simply replace {1} with {$} in your variables.
I had used this approach for many projects. The main advantage is code re-usability and accuracy of expression. I mean my variables are actually underlying expressions and I can control them from one location and re-use them whenever required.
I hope this all makes sense. I understand that this might seem little complex at the beginning...but it will be very easy once you understand approach. Do let me know if you have more questions. I wish I could access some of your sample data to provide QV demonstration 
I haven't answered your question to the 3rd point (regarding 46 day in current QTR). I will do that sometime this evening. Meanwhile you have something to work on
Good luck and sorry for the long past... you might need a break now!!!
Cheers - DV
Alex - No problem at all. This is why we have the forum... to help each other
Yes, if you are declaring the variables in Load Script you need to use Let/Set keywords at the beginning. In our case we will use SET keyword. So you need use...
SET vCurrentQTR = Max({1} QuarterID);
*Note : We also need to use statement terminator ";" at the end of declaration. However, I should have mentioned this to you earlier. I was assuming that you wanted to declare the variable in "Variable Overview" window, (Ctrl + Alt + V) is the keyboard shortcut.
Also most important thing to understand is the QuarterID is the Field Name, which should be in your data model. I mean the idea is to store your time dimensions like this (Just an example)...
Month MonthID Quarter QuarterID Year
Jan 1 Q1 1 2011
Feb 2 Q1 1 2011
Mar 3 Q1 1 2011
Apr 4 Q2 2 2011
May 5 Q2 2 2011
Jun 6 Q2 2 2011
...............
...............
...............
This is the idea to store the data. If you look at my first post with QV example it contains Master Calendar table in the script as an example. So now you have your Month & Quarter in date format and also in number format. So we are using the number format to calculate for previous Quarter and Previou Month.
I hope this helps!
Let me know if you need more informtion.
Cheers - DV
Hi Alex,
Absolutely right! We should not be manually updating the Excel Files. Please use the below script and I had also attached a QV document. Please note that you need to change only the values which are in red (both beginning & end of script). You need to set the vDateMin according to your data in your QV document, Identify the minimum date and then same is the case vDateMax.
The below script also contains CalendarQuarterID to calculate the variables/expression for Current Quarter and Previous Quarter. Also, just to let you know that I had gathered this script from Jason Long's post, all credit to him . Although, I had tweaked it little but for our purpose.
Cheers - DV
Calendar:
LET vDateMin = Num(MakeDate(2010,1,1));
LET vDateMax = Num(MakeDate(2011,12,31));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Ceil(Month(TempDate)/3) AS CalendarQuarterID,
Year(TempDate) AS CalendarYear,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Date Examples
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
LET vDateMin = Num(MakeDate(2010,1,1));
LET vDateMax = Num(MakeDate(2011,12,31));
LET vDateToday = Num(Today());
