topic Re: YTD Sales after selecting a Month in QlikView

YTD Sales after selecting a Month

wgonzalez — Mon, 09 Jul 2018 16:05:52 GMT

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:

sunny_talwar — Mon, 09 Jul 2018 16:08:05 GMT

May be try this

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

wgonzalez — Mon, 09 Jul 2018 17:13:29 GMT

Excellent! Thanks!

Anonymous — Fri, 14 Sep 2018 19:00:34 GMT

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 — Wed, 24 Oct 2018 22:07:26 GMT

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 — Thu, 25 Oct 2018 00:36:20 GMT

May be this

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