Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Im using Chart Properties with the Dimension Customers and Expression
= sum({$<Month={"$(=Date(Max(Month),'MMM-YY'))"}>}Sales)
This works fine for Month to Date (July 1 to 11 2018).
How can i have the same expression for yesterday? e.g July 10, 9....
My Script
OLEDB CONNECT
Orderheader:
LOAD
Day (Date) as DayDate,
Month( Date) as MonthDate,
Year (Date) as YearDate,
Date(Date,'MM-DD-YYYY') as Date,
Date(Date,'MMM-YY') as Month,
Sales,
Customers";
SQL SELECT * FROM Customers;
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident Orderheader;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
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 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
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Hi,
Try this
=Date(today()-1)
will give you yesterday
No it wont work
Hi,
Try this
=Date(Max([Date])-1, 'DD/MM/YYYY')
Thanks,
Naresh
Assuming you are using a pivot table to display the above table with Date as the pivoted dimension, I think what you real need here is Inter-Record Functions.
Hi Naresh,
Did not work.
For yesterday
Sum({ < Day = { $(=Max(Day) - 1)} ,Date >} Sales)
and for MTD the expression would be Month = {"<=$(=num(Month))"} (this would include selected record to date, not previous record)
You looking to have MTD from yesterdays date?
So maybe the whole expression would be
Only({ < Day = { $(=Max(Day) - 1)} ,Date , Day = {"<$(=num(Day))"} >} Sales)
FYI, thanks to stalwar1 for giving this Month = {"<=$(=num(Month))"} <<< expression it saved me life and always worked for me.
Hi Shahbaz,
Sum({ < Day = { $(=Max(Day) - 1)} ,Date >} Sales) - didnt work
Only({ < Day = { $(=Max(Day) - 1)} ,Date , Day = {"<$(=num(Day))"} >} Sales) also didnt work.
Regards,
Mazda
It worked for me...
Sum({ < Day = { $(=Max(Day) - 1)} ,Date , Day = {"<$(=num(Day))"} >} Sales)
Use Sum in above expression not Only.
anyway if it's ok please provide sample data?
Original Equation = sum({$<Month={"$(=Date(Max(Month),'MMM-YY'))"}>}Sales) - MTD
-----------------------------------------------------------------------------------------------------------
Sum(Sales) - Gets Sum for the specific day of the column
sum({$<CurrentDay={"$(=Date(Max(Day),'MMM-YY'))"}>}Sales) - Current Day
sum({$<PreviousDay={"$(=Date(Max(Day)-1,'MMM-YY'))"}>}Sales) - Previous Day
sum({$<DayMinusTwo={"$(=Date(Max(Day)-2,'MMM-YY'))"}>}Sales) - Previous Day - 1
-----------------------------------------------------------------------------------------------------------
(sum({$<PreviousDay={"$(=Date(Max(Day)-1,'MMM-YY'))"}>}Sales)
-
sum({$<CurrentDay={"$(=Date(Max(Day),'MMM-YY'))"}>}Sales) )/100 - Diffbetween Curr Day n Prev Day
(sum({$<DayMinusTwo={"$(=Date(Max(Day)-2,'MMM-YY'))"}>}Sales)
-
sum({$<PreviousDay={"$(=Date(Max(Day)-1,'MMM-YY'))"}>}Sales) )/100 - Diff between PrevDay n PrevDay-1