QlikView Documents

[rahulpawarb]

Hello Friends,

WTD (Week To Date), MTD(Month To Date), QTD (Quarter To Date) & YTD(Year To Date) are commonly used for any kind of analysis. Below are some expression which you can use while doing the same:

Very important to have the DateKey field in number format by using Floor(Date) AS DateKey. This will gives you numeric equivalent of the respective date. Post that you can derive the required expressions. In this case I have created expressions for WTD, MTD, QTD & YTD for current year as well as previous year. Also included Rolling MTD for 12 months.

WTD

//Current Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(WeekStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Sales)

//Previous Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(WeekStart(Max(DateKey),-52))) <=$(=AddYears(Max(DateKey),-1))"}>}Sales)

MTD

//Current Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(Max(DateKey)))) <=$(=Num(Max(DateKey)))"}>}Sales)

//Previous Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddYears(Max(DateKey),-1))))<=$(=Num(AddYears(Max(DateKey),-1)))"}>}Sales)

Rolling MTD

//Current Month

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(Max(DateKey)))) <=$(=Num(Max(DateKey)))"}>}Sales)

//Current Month - 1

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-1)))) <=$(=Num(AddMonths(Max(DateKey),-1)))"}>}Sales)

//Current Month - 2

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-2)))) <=$(=Num(AddMonths(Max(DateKey),-2)))"}>}Sales)

.

.

.

//Current Month - 11

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-11)))) <=$(=Num(AddMonths(Max(DateKey),-11)))"}>}Sales)

QTD

//Current Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(QuarterStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Sales)

//Previous Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(QuarterStart(AddYears(Max(DateKey),-1)))) <=$(=Num(AddYears(Max(DateKey),-1)))"}>}Sales)

YTD

//Current Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(YearStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Sales)

//Previous Year

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(YearStart(AddYears(Max(DateKey),-1)))) <=$(=Num(AddYears(Max(DateKey),-1)))"}>}Sales)

References:

https://community.qlik.com/docs/DOC-6163

https://community.qlik.com/thread/215165

I learned all these by referring documents & discussions generated by all Qlik Community members. Thank so much all of you!

Hope this will be of help.

Regards!

Rahul

[ahaahaaha]

Good information. Thank you.

[#3]

Hi,

Can someone please explain the DateKey and Floor aspect of this to me?

My Date field in my data model is called DATE but Floor(DATE) gives me and error

Also what would I replace [Order Date] with?

[rahulpawarb]

Hello Nicholas,

Trust that you are doing well.

I have used the DateKey field in number format by using Floor([Order Date]). This returned me numeric equivalent of the respective date. I used this to apply date range validation with the help of equivalent number.

//By Replacing [Order Date]

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]={">=$(=YearStart(Max([Order Date]))) <=$(=Max([Order Date]))"}>}Sales)

If your expression is still throwing an error then please share the application with sample data. This will help me to provide you desired response.

Regards!

Rahul

[OmarBenSalem]

Hi Nicholas,

Don't panic, set expressions seems difficult at first sight, but then you'll love working with them.

Let's suppose you have a date field (you don't have to create variables); only a date field will do the job.

And let's suppose you've created a calendar in your script so you have Year,Month, Quarter and Week fields

YTD: if we select 12/04/2016: YTD will return our measure from 01/01/2016 to 12/04/2016

How we do that?

Suppose our measure is : sum(Sales)

1)First changes: sum({<date=,Year=,Month=,Quarter=>}Sales)

We add these to force Qlik to not take into consideration our selection of date for example.

Let me explain in better words, if you don't write the "date=" and select the date 12/04/2016; Qlik will filter all the data to that selected point and then return the sum(Sales) for the day 12/04/2016.

To prohibit this, we must write the date=.

2) Second change: sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

Let explain this : date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}

We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

So we're working with the field :

a) date={    }

b) Now we wanna this date to be <=selected date which is max(date) ;

max(date) is a function so it needs an "=" sign:

=max(date)

when we have a '=' we add the $ (before each calculation) : $(=max(date) ) => this is 12/04/2016

Now we add the <= so we'll have :  <=$(=max(date) )

for the second part, we want our date to be >=01/01/2016 which is the start of the year:

a) same approach, we use the YearStart function that returns the start of the selected year: >=$(=YearStart(Max(date)))

Now our expression is : from : date={    }

to : date={>=$(=YearStart(Max(date)))<=$(=Max(date))}

Now, in a set expression, if we wanna work with the year 2016 for example which is numeric: we call it without quotes:

Year={2016}

If we wanna focus on a country, Tunisia for example which is a string: we call it with quotes

Country={'Tunisia'}

In our case, we focusing on a range of dates created by an expression, so we surround it by double quotes:

date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}

Final expression for YTD:

sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

Same approach for MTD:

sum({<date=,Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)

Hope this helps,

Omar,

[ananyaghosh]

Hi,

If I use date, Year, Month, Quarter columns as dimension, then my MTD, YTD or WTD values will be wrong? Or should I consider those columns as dimension?

Thanks,

Sandip

[roebrich]

Hello,

This is very helpful, I just have follow-up question. How will we implement this in a selection box for example wherein the options will be MTD, QTD, YTD and whichever is selected will affect the calculations on the charts?

Thanks,

Roebrich

[rahulpawarb]

Hello Roebrich,

Apologies for the delay in response.

Considering the previously attached solution as base for your requirement, you can follow below steps:

1. Create an Island Table

     AGGTYPE:

     LOAD * INLINE [

     Type

     WTD

     MTD

     QTD

     YTD

     ];

2. Add List box to show Type. Select anyone of the value and set the Always One Selected Value option present on General tab.

3. Add Conditional Expression for Every expression

     Week Expressions: =If(GetFieldSelections([Type])='WTD',1,0)

     Month Expressions: =If(GetFieldSelections([Type])='MTD',1,0)

     Quarter Expressions: =If(GetFieldSelections([Type])='QTD',1,0)

     Year Expressions: =If(GetFieldSelections([Type])='YTD',1,0)

Hope this will help.

Regards!

Rahul Pawar

[ysj]

Thank you.

[Ceb]

Very helpful. @rahulpawarb 

One question though. 

I tried this with a date filter but it give a value = 0. 

For example if I select a date = January 2020 in the filter, it shows the current year with right values but shows 0 for the previous year. 

Is it possible to make it dynamic(change depending on the date filter)?