Yesterday and Month to Date

[Anonymous]

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; 

[rajasaravananm]

Hi,

Try this

=Date(today()-1)

will give you yesterday

[Anonymous]

No it wont work

[narband2778]

Hi,

Try this

=Date(Max([Date])-1, 'DD/MM/YYYY')

Thanks,

Naresh

[sunny_talwar]

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‌.

[Anonymous]

Hi Naresh,

Did not work.

[MK9885]

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.

[Anonymous]

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

[MK9885]

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?

[groveliam]

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