Skip to main content
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
Showing results for 
Search instead for 
Did you mean: 

Set Analysis for YTD calculation for Select Year and Month?

Hi all,

I am trying to create Set Analysis for an expression where I only want YTD values for the selected Year and Month.

Fields names:

Year: sample values, 2014, 2015, 2016, 2017, 2018 etc

Month: sample values Jan, Feb, Mar, Apr, Jun etc

Expression: Sum(Sales)

So for example if I select Year 2017, and Month April, I want to see the total of Sum(Sales) to include Jan, Feb, Mar, Apr 2017 values added up together.

If I select Year 2015 and July, it should be for all the months up to July 2015.

And so on.

Anybody know how to do this please?

4 Replies
Partner - Creator III
Partner - Creator III

Hi John,

You'll want to try =Sum({<Month = {'<=$(=Max(Month))'}>}Sales)

Note this does asume you are always selecting a year.

Please find an example attached. Is this what you're trying to achieve?

Specialist II
Specialist II

Create a variable for max date & create another variable as Yearstart(Max(Date)) and use these variables in set analysis.

Or you can do it without variable as well.


Hope this helps.

Specialist II
Specialist II

Sorry, there is typo

It should be: Sum({<Date={">=$(=YearStart(Max(Date)))<=$(=Date(Max(Date)))"}>}Value)

Contributor III
Contributor III


Sum( {<Year = {$(=GetFieldSelections(Year))},Month = {$(=GetFieldSelections(Month))}>} Sales)