Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I'm using the following calendar for my analysis.
SUB CALENDAR
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
LET varMinDate = num('1-1-2009');
LET varMaxDate = num('31-12-2017');
TempCalendar:
LOAD
$(varMinDate)+Iterno()-1 AS Num,
Date(($(varMinDate))+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate);
MasterCalendar:
Load
floor(date(TempDate,'DD-MM-YYYY')) AS %key_Date,
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
//YeartoDate(TempDate)*-1 as CurYTDFlag,
//YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
DayNumberOfYear(TempDate) as DayoftheYear,
If( DayNumberOfYear(TempDate) <= DayNumberOfYear(Today()), 'Y', 'N' ) as IsInYTD,
If( DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()), 'Y', 'N') as IsInQTD,
If( Day(TempDate) <= Day(Today()), 'Y', 'N') as IsInMTD,
If( Month(TempDate) = Month(Today()), 'Y', 'N') as IsCurrentMonth,
If( Month(AddMonths(TempDate,1)) = Month(Today()), 'Y', 'N') as IsLastMonth
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
END SUB;
To compare our sales from current month current year vs current month previous year, I used the following script:
SUM(Date ={">=$(=date(addmonths(MonthStart(Today()),-12))) <=$(=date(addmonths(MonthEnd(Today()),-12)))"}[Nett Amount])
Now I want to do the same with quarters instead of months, but the function addquarters does not exist.
Could you guys explain me how to do this comparison?
Thanks a lot in advance!
Mike
Hi,
Try this
SUM(Month=, Quarter=, Year=, Date ={">=$(=date(QuarterStart(Today(),-4))) <=$(=date(QuarterEnd(Today(),-4)))"}[Nett Amount])
Month=, Quarter=, Year=, this will ignore the selections in those fields, since we need to get previous year data we have ignore the current selections of the date filters.
Hope this helps you.
Regards,
jagan.
My quick testing makes me believe an expression like this will give you a starting ground for calculating AddQuarters:
QuarterStart(AddMonths(QuarterStart(Today()),3*<number-of-quarters>))
QuarterEnd(AddMonths(QuarterEnd(Today()),3*<number-of-quarters>))
Clever thinking! Unfortunately this expression shows a null value when using it in my chart..
I tried
QuarterStart(AddMonths(QuarterStart(Today()),3*<4>))
QuarterEnd(AddMonths(QuarterEnd(Today()),3*<4>))
and
QuarterStart(AddMonths(QuarterStart(Today()),3*4))
QuarterEnd(AddMonths(QuarterEnd(Today()),3*4))
Any idea petter-s?:)
Hi,
Try this
SUM(Month=, Quarter=, Year=, Date ={">=$(=date(QuarterStart(Today(),-4))) <=$(=date(QuarterEnd(Today(),-4)))"}[Nett Amount])
Month=, Quarter=, Year=, this will ignore the selections in those fields, since we need to get previous year data we have ignore the current selections of the date filters.
Hope this helps you.
Regards,
jagan.
Hi Jagan,
thanks for your reply! I understand what you're doing, but the chart gives an error for this reference line..
What should I do to figure out what this error is?
Kind regards,
Mike
Can you attach a screenshot of the error? Check whether Month=, Quarter=, Year=, this columns exists in your datamodel if not remove it or rename it.
The expression is correct.
Check this links
Set Analysis for certain Point in Time
Set Analysis for Rolling Periods
Hope this helps you.
Regards,
Jagan.
Thanks Jagan, I checked the expression again and there failed a '}' in the end of my edited expression of your version.
Works perfect now! Thanks a lot:)