Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today

Set Analysis for Rolling Periods

MVP & Luminary
MVP & Luminary

Set Analysis for Rolling Periods

Hi All,

Sometimes there may be requirements from users where they want to see the charts by rolling periods based on the selected periods, please check below for various scenarios.

Note: The format defined in SET DateFormat and your date field format in Qlikview Table should be same, then only this expressions works.

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -4))<=$(=Date(Max(Date)))’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -5))<=$(=Date(Max(Date)))’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -14))<=$(=Date(Max(Date)))’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Max(Date)-9))<=$(=Date(Max(Date)))’}>} Sales )

Note:

YTD - Year to Date

QTD - Quarter to Date

MTD - Month to Date

WTD - Week to Date    

Last YTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))’}>} Sales)


Last QTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -3))’}>} Sales)

Last MTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -1))’}>} Sales)

Last WTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -1))<=$(=Date(Max(Date) -7))’}>} Sales)

Hope this helps.

Regards,

Jagan.

Comments
Luminary
Luminary

Thanks for sharing Jagan, these expression are in great demand and having them here will be very useful to lots of people I'm sure.

Sometimes you're required to do slightly more complex 'As Of' calculations. Last business day for example or perhaps you want a comparative MTD (if Today is the 15th of Sep you want to see results from the 1st to the 15th of August). If this is the case then you may want to check out my post

Calendar with flags making set analysis so very simple

Richard

QlikCentral.Com

Jagan, Nice Article

0 Likes
Valued Contributor II

helpful, thanks.

0 Likes
New Contributor II

This is a really cool little set of set analysis. I (unfortunately) couldn't get it to work and I assume it's because my DateFormat setting is different than the field value's format for some reason.

I figured out a different way to do this. I simply set a variable for X time period in the past and then do the set analysis to evaluate the time period greater than that value.

So for instance, if I want to look back at the past 5 years, I set a variable for 5 years ago like so:

Let v5YearsAgo = Year(Yearstart( Today( ) ) )-4;

Then when I want to use this in set analysis, I simply evaluate it normally:

Sum({<Year={'>=$(v5YearsAgo)'}>} GrossPay )

Hope that helps other folks that may have had trouble getting this to work.

Contributor

Thanks

for Sharing

0 Likes
New Contributor II

Really helpful, I will use this for my app.

Thanks

0 Likes
New Contributor

Hi Jagan

Firstly, thanks for the beautiful post. However, I am not able to fully comprehend the details of it as i am just a beginner here.

I have two columns of information with me. One is the Incident ID (a field) and second one is  a Date Stamp [4/23/2018 6:02:11 AM]  on which the Incident was Created. Like this i have data starting from 1-1-2018 onward until today. 

My questions is, how can I calculate and display the Last 12 month COUNT in bar chart. How can i just display ONLY the last 12 month on my X-Axis and the respective count of incident for those months.

Formula for x-Axis

Formula for Count:

for the count i used the below formula but nothing come up in the graph

Count({< Month=[Submit Date]{>=$(=MonthStart(Max([Submit Date]), -11))<=$(=Date(Max([Submit Date])))}>} [Incident ID])

Your help will be much appreciated.

Cheer - Nitin

0 Likes
New Contributor III

Hi Nitin,

Better late than never.

Here is what I would write:

Count({$<[Submit Date]={">$(=MonthStart(AddMonths(Max([Submit Date]), -12)))<=$(=MonthStart(Max([Submit Date])))"}>}[Incident ID])

 

0 Likes
New Contributor III

Hi all,

I have a question basing what you've been written above.  I would like to count only data for last month from todays date, only for status "Open" I'm just qlik begginner and will appreciate your support

Doc NoDate Last ActionStatus
1031231110/30/2019Open
123154321/1/2019Validated
2343231210/21/2019Open
4235531210/2/2019Validated
187123299/20/2019Open
1245200410/8/2019Processed
123143229/30/2019Open

 

0 Likes
New Contributor III

Hi Maciej29,

Can't you use the global filters? You could select Status=Open and Date Last Action corresponding to the wished month.

If you want to use Set Analysis, I would write it like this:

Count(distinct {$<Status={"Open"}, DateLastAction={">=$(=MonthStart(Today()))<$(=MonthStart(AddMonths(Today(), 1)))"}>}[Doc No])
0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-09-02 11:32 PM
Updated by: