Set Analysis for certain Point in Time

[jagan]

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.

[surajap123]

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?

[jagan]

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.

[#4]

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.

[richard_pearce6]

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]

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]

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]

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

[jagan]

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.

[#10]

very nice post .. thank you jagan

[nevets22]

Sooooo very helpful! Thank you!