topic Re: Set Analysis by Month Question in QlikView

Set Analysis by Month Question

Thu, 16 Jan 2014 17:40:19 GMT

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

Re: Set Analysis by Month Question

Nicole-Smith — Thu, 16 Jan 2014 17:44:18 GMT

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.

Re: Set Analysis by Month Question

Thu, 16 Jan 2014 18:02:11 GMT

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?

Re: Set Analysis by Month Question

Thu, 16 Jan 2014 18:04:04 GMT

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

Re: Set Analysis by Month Question

Gysbert_Wassenaar — Thu, 16 Jan 2014 18:08:34 GMT

Try adding a modifier to disregard the selection in CalendarMonthName:

YTD:

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

Re: Set Analysis by Month Question

Nicole-Smith — Thu, 16 Jan 2014 18:10:03 GMT

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

Re: Set Analysis by Month Question

Thu, 16 Jan 2014 18:13:54 GMT

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."

Re: Set Analysis by Month Question

Thu, 16 Jan 2014 18:14:44 GMT

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