topic Re: Set Analysis for YTD calculation for Select Year and Month? in QlikView

Set Analysis for YTD calculation for Select Year and Month?

jblomqvist — Fri, 05 May 2017 11:29:18 GMT

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?

Re: Set Analysis for YTD calculation for Select Year and Month?

RonaldDoes — Fri, 05 May 2017 12:28:17 GMT

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?

Re: Set Analysis for YTD calculation for Select Year and Month?

neelamsaroha157 — Fri, 05 May 2017 12:47:05 GMT

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.

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

Hope this helps.

Re: Set Analysis for YTD calculation for Select Year and Month?

neelamsaroha157 — Fri, 05 May 2017 15:26:01 GMT

Sorry, there is typo

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

Re: Set Analysis for YTD calculation for Select Year and Month?

ramya_grandhi — Fri, 21 Sep 2018 07:38:46 GMT

John,

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