Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Set Analysis for certain Point in Time

cancel
Showing results for 
Search instead for 
Did you mean: 
jagan
Luminary Alumni
Luminary Alumni

Set Analysis for certain Point in Time

Last Update:

Sep 20, 2022 1:59:53 PM

Updated By:

Sue_Macaluso

Created date:

Aug 29, 2015 8:41:17 AM

Hi All,

Sometimes there may be requirements from users where they want to see the charts in from certain point of time like YTD, QTD, MTD, Last 5 Years etc., please find the set analysis expressions for this type of scenarios.

YTD Sales (Year To Date)

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

Note: Year=, Quarter=, Month=, Week= excludes the selections in Year, Quarter, Month and Week dimensions.

QTD Sales (Quarter To Date)

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

MTD Sales (Month To Date)

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

WTD Sales (Week To Date)

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

Last 5 Years Sales

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

Last 6 Quarters Sales

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

Last 12 Months Sales

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

Last 15 Weeks Sales

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

Last 10 Days Sales

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

Yesterday Sales

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

You can also arrive some flags for above scenarios in script and you use those flags in Set Analysis expression if your data always based on Current Date. Refer below link created by Richard.Pearce60

Calendar with flags making set analysis so very simple

Hope this helps.

Regards,

Jagan.

Comments
surajap123
Creator II
Creator II

Thanks for sharing.

One question, I always confuse with terms-

-->Point in time

-->Rolling

What we see here is point in time reporting, then what is rolling time analysis and when it is used?

0 Likes
jagan
Luminary Alumni
Luminary Alumni

Rolling n period means based on selection, for example if year 2015 is selected then rolling 5 years is 2011 to 2015, if 2014 is selected then 2009 to 2014, this is called Rolling n periods.

Where as point in time over certain period like MTD, YTD, Last week, yesterday etc.

Not applicable

Is anything loaded in your script, or would I be able to replace your fields with mine?  I have fields for date, week, month, and quarter in my data.

0 Likes
richard_pearce6
Luminary Alumni
Luminary Alumni

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


Richard

QlikCentral.Com

jagan
Luminary Alumni
Luminary Alumni

Hi Richard,

I know about your post regarding the master calendar with various flags. I

used it in one of my project. All I want to say in this post is how to

deal this in set analysis.

I am big fan of you, I regularly follow your posts and they are so nice and

very descriptive.

richard_pearce6
Luminary Alumni
Luminary Alumni

Thanks Jagan,

Really like your posts too.

Hopefully you don't mind me linking to mine also as I saw a crossover for people searching for these types of techniques. I've added links to both your posts to mine also (currently in moderation)

Take care

Richard

jagan
Luminary Alumni
Luminary Alumni

I am glad that you have posted my links in your post. 

jagan
Luminary Alumni
Luminary Alumni

Hi Richard,

I updated my post with your link which deals with various date flags, so that it helps people who want to implement the same in scripting. 

Thank you.

Regards,

Jagan.

Not applicable

very nice post .. thank you jagan

nevets22
Contributor III
Contributor III

Sooooo very helpful! Thank you!

0 Likes
Contributors
Version history
Last update:
‎2022-09-20 01:59 PM
Updated by: