topic Re: CUMULATIVE YEAR WITH MASTERCALENDAR in QlikView

CUMULATIVE YEAR WITH MASTERCALENDAR

Fri, 07 Nov 2014 15:23:23 GMT

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

Re: CUMULATIVE YEAR WITH MASTERCALENDAR

MK_QSL — Fri, 07 Nov 2014 15:40:33 GMT

SUM(

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

TCMSALESORDERMonth=,

TCMSALESORDERDay=,

TCMSALESORDERMonthYear

Amount)

Re: CUMULATIVE YEAR WITH MASTERCALENDAR

Fri, 07 Nov 2014 16:02:38 GMT

the result ist 0

i have no idea

Re: CUMULATIVE YEAR WITH MASTERCALENDAR

MK_QSL — Fri, 07 Nov 2014 16:48:42 GMT

What is the format of your TCMSALESORDERDATE?

Re: CUMULATIVE YEAR WITH MASTERCALENDAR

MarcoWedel — Fri, 07 Nov 2014 23:37:57 GMT

please post sample app

Re: Re: CUMULATIVE YEAR WITH MASTERCALENDAR

Sat, 08 Nov 2014 18:24:23 GMT

hi here my report!

Re: CUMULATIVE YEAR WITH MASTERCALENDAR

preminqlik — Sat, 08 Nov 2014 19:13:59 GMT

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)

Re: Re: Re: CUMULATIVE YEAR WITH MASTERCALENDAR

MarcoWedel — Sun, 09 Nov 2014 12:47:08 GMT

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

Re: Re: Re: CUMULATIVE YEAR WITH MASTERCALENDAR

Mon, 10 Nov 2014 09:52:20 GMT

Hi Marco,

thanx for your script i tried ite and it works.

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

Re: Re: Re: CUMULATIVE YEAR WITH MASTERCALENDAR

preminqlik — Mon, 10 Nov 2014 10:39:15 GMT

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)