Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have got the devil inside
this is my mastercalendar date script
OrderCalendar:
Load
TempDate AS TCMSALESORDERDATE,
week(TempDate) As TCMSALESORDERWeek,
Year(TempDate) As TCMSALESORDERYear,
Month(TempDate) As TCMSALESORDERMonth,
Day(TempDate) As TCMSALESORDERDay,
YeartoDate(TempDate)*-1 as TCMSALESORDERCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as TCMSALESORDERLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as TCMSALESORDERRC12,
date(monthstart(TempDate), 'MM-YYYY') as TCMSALESORDERMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as TCMSALESORDERQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as TCMSALESORDERWeekYear,
WeekDay(TempDate) as TCMSALESORDERWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
i want for example the current year from jan - nov sum(amount) and this is my expression
=SUM({<TCMSALESORDERYear={'$(vMaxYear)'},TCMSALESORDERDATE={'<=$(vMaxDate)'},TCMSALESORDERMonth=,TCMSALESORDERDay=,TCMSALESORDERMonthYear=>} AMOUNT)
why is the result 0 ??
if i understand try this below one
(always makesure that variables which you are using test in text box)
SUM({<INVOICEDATE={">=$(=Yearstart(max(INVOICEDATE)))<=$(=max(INVOICEDATE))"},INVOICEYear=,INVOICEMonth=,INVOICEDay=>} INVOICEAMOUNTMST)
SUM(
{<
TCMSALESORDERDATE = {">=$(=YearStart(Max(TCMSALESORDERDATE)))<=$(=Today())"},
TCMSALESORDERMonth=,
TCMSALESORDERDay=,
TCMSALESORDERMonthYear
>}
Amount)
the result ist 0
i have no idea
What is the format of your TCMSALESORDERDATE?
please post sample app
hi here my report!
if i understand try this below one
(always makesure that variables which you are using test in text box)
SUM({<INVOICEDATE={">=$(=Yearstart(max(INVOICEDATE)))<=$(=max(INVOICEDATE))"},INVOICEYear=,INVOICEMonth=,INVOICEDay=>} INVOICEAMOUNTMST)
Hi,
although your question is already answered, I tried to create a shorter version of your calendar script:
OrderCalendar:
LOAD Date as TCMSALESORDERDATE,
Week(Date) as TCMSALESORDERWeek,
Year(Date) as TCMSALESORDERYear,
Month(Date) as TCMSALESORDERMonth,
Day(Date) as TCMSALESORDERDay,
YeartoDate(Date)*-1 as TCMSALESORDERCurYTDFlag,
YeartoDate(Date,-1)*-1 as TCMSALESORDERLastYTDFlag,
InYear(Date, Monthstart(MaxDate),-1) as TCMSALESORDERRC12,
Date(monthstart(Date), 'MMM-YYYY') as TCMSALESORDERMonthYear,
'Q'&Ceil(Month(Date)/3) as TCMSALESORDERQuarter,
Week(WeekStart(Date)) & '-' & WeekYear(Date) as TCMSALESORDERWeekYear,
WeekDay(Date) as TCMSALESORDERWeekDay;
LOAD Date(MinDate+IterNo()-1) as Date,
MaxDate
While MinDate+IterNo()-1<=MaxDate;
LOAD Min(TCMSALESORDERDATE) as MinDate,
Max(TCMSALESORDERDATE) as MaxDate
Resident OrderHeader;
InvoiceCalendar:
LOAD Date as INVOICEDATE,
Week(Date) as INVOICEWeek,
Year(Date) as INVOICEYear,
Month(Date) as INVOICEMonth,
Day(Date) as INVOICEDay,
YeartoDate(Date)*-1 as INVOICECurYTDFlag,
YeartoDate(Date,-1)*-1 as INVOICELastYTDFlag,
InYear(Date, Monthstart(MaxDate),-1) as INVOICERC12,
Date(monthstart(Date), 'MMM-YYYY') as INVOICEMonthYear,
'Q'&Ceil(Month(Date)/3) as INVOICEQuarter,
Week(WeekStart(Date)) & '-' & WeekYear(Date) as INVOICEWeekYear,
WeekDay(Date) as INVOICEWeekDay;
LOAD Date(MinDate+IterNo()-1) as Date,
MaxDate
While MinDate+IterNo()-1<=MaxDate;
LOAD Min(INVOICEDATE) as MinDate,
Max(INVOICEDATE) as MaxDate
Resident Invoices;
maybe it helps
regards
Marco
Hi Marco,
thanx for your script i tried ite and it works.
can u help me with the cumulative column of the prev year?
see previous year depends on which variables you prepare
compose chart and write the below expression , take anything as a dimension without date unit and dont label the expression for a while.hover your mouse into heading of expression , it will show the values of the below expressions (cross check)
SUM({<INVOICEDATE={">=$(=Yearstart(max(INVOICEDATE),-1))<=$(=makedate(Year(max(INVOICEDATE))-1,num(Month(max(INVOICEDATE))),Day(max(INVOICEDATE))))"},INVOICEYear=,INVOICEMonth=,INVOICEDay=>} INVOICEAMOUNTMST)