Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to calculate current year and previous year sales using set analysis .
Master calendar is
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(ShipDate_1) as minDate,
max(ShipDate_1) as maxDate
Resident SalesData;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
let vPriorMonthYear = '=Year(addmonths(max(GasDay),-1))';
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS ShipDate_1,
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), 'MM/DD/YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
//Let vPriorMonth='=Month(addmonths(Max(ShipDate_1),-1))'
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
try this
Current Year
=SUM({$<Date = {'>=$(=YearStart(Today()))<=$(=MonthEnd(Today()))'}>}Sales)
Previous Year
=SUM({$<Date = {'>=$(=YearStart(AddYears(Today(),-1)))<=$(=MonthEnd(AddYears(Today(),-1)))'}>}Sales)
Hi,
Current Year
Sum({$<Date={">=$(=Year Start(Max(Today())))<=$(=Max(Today()))"}>}Sales)
Previous Year
Sum({$<Date={">=$(=Year Start(Add Years(Max(Today(),-1))))<=$(=Add Years(Max(Today())))"}>}Sales)
I can think this?
Current_Year
Sum({<Year = {$(=Max(Year))}>} Sales)
Last_Year
Sum({<Year = {$(=Max(Year-1))}>} Sales)
Hi Rohini,
Try this.
In MasterCalendar: add this "Num(Month(TempDate)) As MonthNum" (in script level)
Expression is:
Current Year YTD:
Sum({<Year={$(=max(Year))},MonthNum={'<=$(=max({<Year=$(=max(Year))}>}MonthNum))'}>}Sales)
Previous Year YTD:
Sum({<Year={$(=max(Year)-1)},MonthNum={'<=$(=max({<Year=$(=max(Year))}>}MonthNum))'}>}Sales)
Thanks,
Gopi