Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
wgonzalez
Contributor

YTD Sales after selecting a Month

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:

    

YearMonthMonthEndDateSales
2018011/1/183,461.40
2018022/1/183,839.57
2018033/1/183,676.50
2018044/1/183,938.82
2018055/1/184,667.89
2018066/1/18533.75
2018077/31/180.00
2018088/31/180.00
2018099/30/180.00
20181010/31/180.00
20181111/30/180.00
20181212/31/180.00
Tags (2)
1 Solution

Accepted Solutions

Re: YTD Sales after selecting a Month

May be try this

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

5 Replies

Re: YTD Sales after selecting a Month

May be try this

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

wgonzalez
Contributor

Re: YTD Sales after selecting a Month

Excellent!  Thanks!

Highlighted
camron_allan
New Contributor II

Re: YTD Sales after selecting a Month

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
Contributor

Re: YTD Sales after selecting a Month

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.

Re: YTD Sales after selecting a Month

May be this

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