Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS

YTQ, QTD, MTD and WTD

YTQ, QTD, MTD and WTD

Year, Quarter, Month and Week To Date are the common analysis that I seen many applications. I will share the expression to do here

First to do this your data model should have the DateField in number format by applying floor

Similar to this

Floor(DateField) AS DateNum //it will gives you one whole number to represent date

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(QuarterStart(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)

if you want you can set a variable value as 'Year', 'Month', 'Quarter', 'Week', lets say vToDate and go with single chart and single expression

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

Will keep your expression simple

Regards,

Celambarasan

Comments
Specialist II
Specialist II

Hi

You can achieve this

Refer this

Also good thing about qlik is you can use the expressions in same Qlik doc or in a different Qlik document from external source.

Refer the below link

Reusing Expressions

Not applicable

Excellent!! I was missing the $ sign before the variable! that do the trick!

thanks!

0 Likes
Partner
Partner

Excellent Work Celambarasan. good job!!

0 Likes
Not applicable

how is the expression done for Qlik Sense i am total new beginner and i need an expression for

order_date.autocalendar.yearmonth   january 2015 to Febuary 2015


but i dont know where to add in the example expression below


sum({$<[Order_Day.autoCalendar.YearMonth] = {'$(=date(max([Order_Day.autoCalendar.YearMonth]), 'YYYY-MMM'))'}>} [DPO_Data.Amount])

0 Likes
Creator II
Creator II

Hi

As a beginner I didn't understand

How  Year=, Month=, Quarter=, Week=, DateField= 

this works.

Can any one explain?

0 Likes
Partner
Partner

Its like telling not to consider the selections applied for Year, Month, Quarter, Week, DateField.

Cheers,

Naresh

0 Likes
Creator II
Creator II

thank you guntur

If  I select Year=, Month=, Quarter=, Week=, DateField= 

Values  are changing .

celambarasan,


Nice.


But in my pivot table i have week Values. How to convert YTD based at Week

0 Likes
Partner
Partner

Hi All

Is there a way to default a selection to a date so when a user opens a dashboard the KPIs having meaning?

Best, Paul

0 Likes
Master II
Master II

Good Work

Thank's for sharing

Saludos,

Enrique

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-03-21 03:06 AM
Updated by: