Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
Need to create current vs previous qurter..
For Example:
If I select 2016 Q1 then Output should be --- 2016 Q1 & 2015 Q4.
If I select 2015 Q3 then Output should be --- 2015 Q3 & 2015 Q2.
Kindly help me on this..
Regards,
Helen
Based on the given data 2009 is the max year, will see Max qtr & Prev qtr
Regards,
Helen
Current Quarter: Sum(Amount)
Previous Quarter: Sum({<MyQuarter#={'$(=QuarterStart(Max(MyQuarter),-1))'}>}Amount)
HI
PFA
Hi Helen,
try to use Master Calender refer below code...
T1:
LOAD OrderID,
ProductID,
Product,
CustomerID,
EmployeeID,
OrderDate,
OrderDate as #DateKey,
Margin,
Sales,
Cost,
Quantity,
F11
FROM
(biff, embedded labels, table is Orders$);
QuartersMap:
MAPPING LOAD * Inline [
Month, Q
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];
MonthMap:
Mapping LOAD * Inline [
MonthNo, LongMonth
1, January
2, February
3, March
4, April
5, May
6, June
7, July
8, August
9, September
10, October
11, November
12, December
];
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident T1;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(YearEnd(Today()));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Set vFM = 4 ;
MasterCalendar:
LOAD
TempDate AS #DateKey,
TempDate AS #HDateKey,
week(TempDate) as MasterWeek,
Year(TempDate) as MasterYear,
Year(TempDate) -1 as [MasterPriorYear],
Month(TempDate) as MasterMonth,
MonthStart(TempDate) as StartDate,
MonthEnd (TempDate) as EndDate,
Day(TempDate) as MasterDay,
YeartoDate(TempDate)*-1 as MasterCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as MasterLastYTDFlag,
ApplyMap('QuartersMap', month(TempDate), Null()) as MasterQuarter ,
Autonumberhash256 (ApplyMap('QuartersMap', month(TempDate), Null()),Year(TempDate))as QuarterNumber
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Sum({<QuarterNumber={'$(=Max(QuarterNumber))'}>}Sales)
sum({1<QuarterNumber={'$(=Max(QuarterNumber)-1)'}>}Sales)
you can try this,
in script add : dual(year(OrderDate)&'Q'&Ceil(Month(OrderDate)/3),QuarterStart(OrderDate))as MyQuarter,
Create straight table: Dimension as Employee ID
Expression as
Curr Q Sales =Sum(Sales)
Pre Q Sales = sum({1<MyQuarter={"$(=dual(year(max(OrderDate))&'Q'&Ceil((Month(max(OrderDate))/3)-1),QuarterStart(max(OrderDate))))"}>}Sales)