topic Set Analysis - YTD on Selected Month in QlikView

Set Analysis - YTD on Selected Month

Tue, 30 Jun 2009 00:41:43 GMT

I am trying to create a YTD field so that the column will only show the YTD values given the current month selection. For example, if you select "March" (a date field) the column will sum up January, February and March. The calculation will change depending on the current selection. I have seen some posts, but no sample codes. I cannot seem to get it to sum a range when only one month is selected.

Set Analysis - YTD on Selected Month

johnw — Tue, 30 Jun 2009 03:55:51 GMT

I don't know if this is exactly what you're asking for, but it is a YTD total based on the maximum month currently selected (so if no month is selected, it is a normal YTD). Here's the expression:

=sum({<Month={'>=$(=date(yearstart(max(Month)),'MMM-YY')) <=$(=date(max(Month),'MMM-YY'))'}>}Value)

Set Analysis - YTD on Selected Month

Wed, 01 Jul 2009 00:19:38 GMT

Thanks John!

I was trying to get the "<=" sign to work but could not get it to take. I had to modify what you gave me and got it to work. Below is my modified version where MonthNum is the number of the month:

=sum({$ < [Group_ID] = {'B'}, Year = {$(=Year(Today()))}, MonthNum = {">= {'1'} <=$(=max(MonthNum))"}, Month= >}Hours)

Thanks again!

Re: Set Analysis - YTD on Selected Month

Wed, 27 Jun 2012 09:35:34 GMT

thanks John, that helped me too (Y)

Re: Set Analysis - YTD on Selected Month

rohit214 — Wed, 27 Jun 2012 09:55:00 GMT

Hi John,

If my Fiscal year starts from 1-apr-2012 to 31-03-3012 then

what will be the expression for same condition???

Thanks & regards

rohit

Re: Set Analysis - YTD on Selected Month

Wed, 27 Jun 2012 10:06:16 GMT

Hi Rohit

then you just change the MonthNum = {">= {'1'} part to 4.

Re: Set Analysis - YTD on Selected Month

Fri, 06 Jul 2012 15:41:42 GMT

Hi John,

I'm trying to set a sheet activation trigger for a Year to Date sales chart. I simply want the trigger to select all months from January to the current month, but the syntax is killing me!

I tried (for example):

=sum({<CloseMonth = {"<= month(today()"}>}1)

and

=sum({<CloseMonth = {"CloseMonth <= month(today()"}>}1)

In both cases, the syntax checker said the expression was OK, but when I leave and re-enter the sheet, nothing is selected in the CloseMonth listbox. "CloseMonth" is a dual field, which shows as text, but has the standard numerical value. Any thoughts?