Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Previous YTQ, QTD, MTD and WTD

celambarasan
Not applicable

Previous YTQ, QTD, MTD and WTD

In reference to YTQ, QTD, MTD and WTD

This is to a analysis of Sales up to the Selected day in Previous year.

YTD - Year To Date

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

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

Expression would be

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

Previous QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(QuarterStart(Max(DateNum), -4)))<=$(=AddYears(Max(DateNum), -1))"}>} Sales)

Previous MTD- Month to Date

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

Previous WTD- Month to Date

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


Let me know if there is any concerns.

Labels (2)
Comments
richard_pearce6
Not applicable

Thanks for the post. I've created a calendar script which help make set analysis for time periods very simple. It can be found here:

http://community.qlik.com/docs/DOC-6593

Regards

Richard

QlikCentral.com

kkkumar82
Not applicable

Hi celambarasan,

I have a data from 1- oct - 2012 to 1 - oct - 2015 (prediction) and a measure like spend, i have requirement to create a chart where the chart has to spend of

1. spend of  present quarter , spend of next quarter

2. summation of spend of present + previous quarters, summation of spend of next four quarters

All the above are based on the current date.

for eg as of today

1. spend of present quarter is = sum(2014 ,q4  spend)

2. spend of next quarter is = sum(2015,q1 spend)

3. summation of spend of present + previous quarters =  sum(2014 q1 + 2014 q2 + 2014 q3 + 2014 q4 spend)

4. summation of spend for next four quarters = sum(2015 q1 + 2015 q2 + 2015 q3 + 2015 q4 spend)

For 1 & 2 I have tried variables  in set analysis, I could some how trace it with 6 or 7 statements in the load script but I was unable to trace 3 and 4.

Could u help me in this regard.

Thank You.

kkkumar82
Not applicable

Finally I could resolve it by my own by using Quarterstart() and Quarterend() functions, any way I got this idea

from your post .

One more think I have been into Qlikview for the past 2years or so but never seen good articles on dynamic update like stuff, can you help the community people , what is it and where to use.

Thank You.

celambarasan
Not applicable
dinosaur91
Not applicable

Hi, I've got question about WTD

rest is working perfectly.

But with WTD i've got problem that if in 24th week in 2016 is  between 13-19.06

and -52 weeks so it's 25th week in 2015 was between 15-21.06.

IF i select date 19.06.2016

WTD this year will show it ok 13-19.06.16 its 7 days

Previous WTD will show 15-19.06.15 its 5 days

Anyone idea how to show previous WTD between 15-21.2015?

celambarasan
Not applicable

Try the below one and let me know if it works

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

dinosaur91
Not applicable

Thanks for answer it's really helpful,

i used :

Sum({<Year=, Month=, Week=, DataNum={">=$(=Num(WeekStart(Max(DataNum),-52)))<=$(=Num(WeekEnd(AddYears(Max(DataNum),-1))))"}>}Sales)

and its working.

Not applicable

I'm trying to add the expression below for Prior YTD and its appear to sum all the data from 2015-2016

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

All of the original expression for 2016 work perfectly. For some reason all prior year values are including 2016 data. Is there something I need to adjust to limit just to prior year ?

thanks

dinosaur91
Not applicable

Hi Pasquale,

Well, I'm using :

Sum({<Day=,Year=, Month=, Quarter=, Week=, DataRachunku=,Data=,DataNum={">=$(=Num(YearStart(CD.Date, -12)))<=$(=Num(AddYears(CD.Date, -1)))"}>}Sales)
where CD.Date= Today()-1

Maybe it will help in ur case.

Greetings

piotrlipski
Not applicable

Pasquale,

My answer is not so quick but I had the same issue and spend some time on it. In original formula we have missing conversion max date to number format. After this change PYTD works fine in my app. .

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

Version history
Revision #:
1 of 1
Last update:
‎08-27-2014 03:40 AM
Updated by: