Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Expression Help please

My client requirement is to calculate YTD and MTD values as i'm new to qlik please help

Please find attached QVF

Tags (1)
1 Solution

Accepted Solutions

Re: Expression Help please

Check the attached

YTD

=Sum({<OrderDate = {"$(='>=' & YearStart(Max(OrderDate), 0, 4) & '<=' & Max(OrderDate))"}, MonthYear, Year, Month>}Sales)

MTD

=Sum({<OrderDate = {"$(='>=' & MonthStart(Max(OrderDate)) & '<=' & Max(OrderDate))"}, MonthYear, Year, Month>}Sales)

2 Replies

Re: Expression Help please

Check the attached

YTD

=Sum({<OrderDate = {"$(='>=' & YearStart(Max(OrderDate), 0, 4) & '<=' & Max(OrderDate))"}, MonthYear, Year, Month>}Sales)

MTD

=Sum({<OrderDate = {"$(='>=' & MonthStart(Max(OrderDate)) & '<=' & Max(OrderDate))"}, MonthYear, Year, Month>}Sales)

sonysree88
Contributor

Re: Expression Help please

Hi Expressions would be like as below for YTD,QTD,MTD

YTD - Year To Date

A date should be selected and it will look for the Starting date of the year to the selected date.

Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

MTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

WTD- Week to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

Community Browser