Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

CUMULATIVE YEAR WITH MASTERCALENDAR

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

1 Solution

Accepted Solutions
preminqlik
Specialist II
Specialist II

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)

View solution in original post

9 Replies
MK_QSL
MVP
MVP

SUM(

     {<

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

          TCMSALESORDERMonth=,

          TCMSALESORDERDay=,

          TCMSALESORDERMonthYear

     >}

Amount)

Not applicable
Author

the result ist 0

i have no idea

MK_QSL
MVP
MVP

What is the format of your TCMSALESORDERDATE?

MarcoWedel

please post sample app

Not applicable
Author

hi here my report!

preminqlik
Specialist II
Specialist II

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)

View solution in original post

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

Not applicable
Author

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
Specialist II
Specialist II

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)