CUMULATIVE YEAR WITH MASTERCALENDAR

[#1]

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 ??

[MK_QSL]

SUM(

     {<

          TCMSALESORDERDATE = {">=$(=YearStart(Max(TCMSALESORDERDATE)))<=$(=Today())"},

          TCMSALESORDERMonth=,

          TCMSALESORDERDay=,

          TCMSALESORDERMonthYear

     >}

Amount)

[#3]

the result ist 0

i have no idea

[MK_QSL]

What is the format of your TCMSALESORDERDATE?

[MarcoWedel]

please post sample app

[#6]

hi here my report!

[preminqlik · Accepted solution]

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)

[MarcoWedel]

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

[#9]

Hi Marco,

thanx for your script i tried ite and it works.

can u help me with the cumulative column of the prev year? 

[preminqlik]

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)