topic Re: Expression Help please in QlikView

Expression Help please

Thu, 25 May 2017 17:37:06 GMT

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

Please find attached QVF

sunny_talwar — Thu, 25 May 2017 17:50:12 GMT

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 — Fri, 26 May 2017 10:00:37 GMT

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)