YTD Sales after selecting a Month

[wgonzalez]

Hi,

I have a pivot table and I want to show the YTD sales after the user has selected a specific Year and Month.

I'm using this expression, but it's just summing until last month's data.  For example if selected June, it's summing Jan through May.

Any suggestions?

Sum({<MonthEndDate={">=$(=YearStart(Max(MonthEndDate)))<=$(=Date(Max(MonthEndDate)))"}>}Sales)

The data in the table is:

Year	Month	MonthEndDate	Sales
2018	01	1/1/18	3,461.40
2018	02	2/1/18	3,839.57
2018	03	3/1/18	3,676.50
2018	04	4/1/18	3,938.82
2018	05	5/1/18	4,667.89
2018	06	6/1/18	533.75
2018	07	7/31/18	0.00
2018	08	8/31/18	0.00
2018	09	9/30/18	0.00
2018	10	10/31/18	0.00
2018	11	11/30/18	0.00
2018	12	12/31/18	0.00

[sunny_talwar · Accepted solution]

May be try this

Sum({<MonthEndDate={">=$(=YearStart(Max(MonthEndDate)))<=$(=Date(Max(MonthEndDate)+1))"}>}Sales)

[wgonzalez]

Excellent!  Thanks!

[Anonymous]

These types of time-related set expressions can get so messy.

You should try QlikRTP!  It's free.

You can read about it in my post, here.

RTP stands for Relative Time Periods.  This script leverages your existing month dimension to add the following relative time elements to your app:

• To-Date ranges such as YTD and QTD
• Relative time periods such as "Previous Year", "Previous Month", or “same period last year”
• Rolling periods which are used for moving averages. (Ex. "Rolling 3 month average")
• Year-over-Year and MoM growth metrics

Not only that, these RTPs will work for any anchor month! (not just the current period)

This approach greatly simplifies your measures by only requiring two set modifiers.

[wgonzalez]

Now I need the sum of the whole year, no matter the selected month.  Could you please help?

I'm using this expression, but once any month is selected it will sum the first 11 months of the year:

=Sum({<MonthEndDate={">=$(=YearStart(Max(MonthEndDate)))<$(=YearEnd(Max(MonthEndDate)+1))"}>}Budget)

Thanks.

[sunny_talwar]

May be this

=Sum({<MonthEndDate = {">=$(=YearStart(Max(MonthEndDate)))<=$(=Date(Floor(YearEnd(Max(MonthEndDate)))))"}>} Budget)