topic SET Analysis - Done with Month Number , Does not work when Selection is by Month Name in QlikView

SET Analysis -By MonthID/MontNumber (Does not work when MonthName is selected)

Wed, 02 Mar 2011 11:25:53 GMT

Hi Everyone,

Requirement - Chart Showing Sales for 6 Months - Selected Month -2, Selected Month, Selected Month +2.

The above requirement has been achieved using the MonthID/MonthNum as a numeric value is required to set the Month Range. (QVW Attached)

Now when I Add a Listbox with MonthNum it works fine.

When I Add a Listbox with MonthName it fails.

The Month names are in the format : JAN FY2011 so a direct addmonths can not be applied or if it can be please let me know how.

Regards,

Chinmay

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Miguel_Angel_Baeyens — Wed, 02 Mar 2011 11:41:11 GMT

Hello Chinmay,

Your expression should look like the following:

Sum({< Date = {'>=$(=MonthStart(Max(Date)))<=$(=MonthEnd(Max(Date)))'} >} Amount)

Max(Date) without selections will return the Sum of Amount between the first and last day of the highest month. When you select any month, the set analysis will return all values between the first and last day of the selected month.

Hope that helps.

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Wed, 02 Mar 2011 12:14:22 GMT

Hi Miguel,

My Calculations are working fine.

Its just the Month Name selection.

Please have a look at the attached QVW file in the original post, and try selecting the MonthName.

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Miguel_Angel_Baeyens — Wed, 02 Mar 2011 12:50:46 GMT

Hi,

Since values in MonthName are strings, not numerics, the opeartors ">=" and ">=" will not work. That's why creating a very simple master calendar with my expression above will work with a minimum effort.

There may be some other solution using Dual() but that means to change every month number and name in the charts.

Hope that helps.

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Wed, 02 Mar 2011 12:53:51 GMT

The MonthNumber and MonthName are both in the same table.

So when i Select the MonthName the Month Number should also get filtered right ?

Will look at your Master Calender. The only thing is the SET analysis has to work with the selections and not max of any dates.

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Wed, 02 Mar 2011 13:15:00 GMT

I checked your master calender in which you set the date in the proper format.

My Date selection is in the format : JAN FY2011 which cant be used for adding date ranges / using addmonths function.

So I am using MonthNumber.

Please suggest a solution with the existing QVW attached.

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Wed, 02 Mar 2011 15:54:37 GMT

Someone please help me in this.

My Selection would be in the format JAN FY2011, FEB FY2011 etc.

How can I add months to such a format. Please check the attached QVW file to understand the issue.

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Wed, 02 Mar 2011 17:08:54 GMT

The Requirement is to use the MonthID which is a numeric value in the Set Analysis syntax.

But have the Month Name as the selection.

Why I need this is there are few reports which are in Fiscal weeks, and users would always have a set number of options to choose from like e.g last 6 Months for the Monthly Reports or last 52 Weeks in weekly reports.

The data is not in days, it only in months so the date fields would have JAN FY2010.

I am just getting more an more curious to solve this.

Help is really appreciated.

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

deepakk — Thu, 03 Mar 2011 13:05:38 GMT

Try this code.

SUM({$<MonthNumber = {'>$(StartDate)<=$(CurrDate)'},MonthName=>} Sales)+ SUM({$<MonthNumber = {'>=$(CurrDate)<=$(EndDate)'},MonthName=>} Sales_Future)

SET Analysis - Done with Month Number , Does not work when Selection is by Month Name

Thu, 03 Mar 2011 17:10:27 GMT

Thanks a lot Deepak...

U Rock !!!