Set Analysis by Month Question

[#1]

Hi all,

I'm creating a report in which I want the ability to select a date/year and display current month and YTD budget/spend numbers. Ideally, I would select a year and month (let's say June 2013), and all of my analytics would update accordingly.

The "Current Month" formula works perfectly - when you select June 2013, it shows only data for that month. I'm having issues with the YTD formula. When I select June 2013, I want to show all spend numbers for Jan-Jun. I'm trying to get set analysis to include June AND any months of data that are less than June. Right now, all I'm able to do is to get it to completely ignore the month selection (show all 12 months), or show only the current selected month. I've tried the below:

Ignore month selection =sum({<CalendarYear = {"$(=max(CalendarYear))"}, CalendarMonthNum = >} BudgetAmount)

Show only max month =sum({<CalendarYear = {"$(=max(CalendarYear))"}, CalendarMonthNum = {"$(=max(CalendarMonthNum ))"}>} BudgetAmount)

Attempt at what I want =

Sum({<CalendarYear = {"$(=max(CalendarYear))"}, CalendarMonthNum = {"<=$(=max(CalendarMonthNum))"}>} BudgetAmount)

Nothing has worked so far. The bottom one only shows YTD if you explicitly select Jan-Jun. I want the ability to only select June and see the YTD numbers. This is how it will be used, so I'd prefer to keep this functionality (rather than having user select a range of months).

Thanks

[Nicole-Smith]

What you have should work (see the attached .qvw).

If it isn't working for you, you will need to post a sample .qvw so we can see what is actually going on.

[#3]

Ok, thank you, you're right: my formula does work - IF you are using the "CalendarMonthNum" list box to select from. I'm actually using another variable, "CalendarMonthName," which is simply the three-letter text representation of the month (it's still linked to CalendarMonthNum). I think it's nicer for the user to select from the text representation, but I use the number value in set analysis because, in as much as I've tried, set analysis gets pretty funky when you try to compare and run functions on string month values.

Correct:

Incorrect:

Any idea on a work around?

[#4]

I should also mention that you should only pay attention to the "YTD Budget" columns above.

[Gysbert_Wassenaar · Accepted solution]

Try adding a modifier to disregard the selection in CalendarMonthName:

YTD:

Sum({<CalendarYear = {"$(=max(CalendarYear))"}, CalendarMonthNum = {"<=$(=max(CalendarMonthNum))"}, CalendarMonthName= >} BudgetAmount)

[Nicole-Smith]

Gysbert Wassenaar's solution works.  He beat me to it

[#7]

I figured it out! See below:

Sum({<CalendarYear = {"$(=max(CalendarYear))"}, CalendarMonthNum = {"<=$(=max(CalendarMonthNum))"},

CalendarMonthName = >} BudgetAmount)

I just needed to have it ignore the CalendarMonthName selection by adding "CalendarMonthName = " in the set analysis expression. I think that takes care of it! Thanks for you help - your example got me "unstuck."

[#8]

Yep, I came to this realization as you posted this response. Thanks!