Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
deepakk
Partner - Specialist III
Partner - Specialist III

hi

Try this code.

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



View solution in original post

9 Replies
Miguel_Angel_Baeyens

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.

Not applicable
Author

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.

Miguel_Angel_Baeyens

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.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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.

deepakk
Partner - Specialist III
Partner - Specialist III

hi

Try this code.

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



Not applicable
Author

Thanks a lot Deepak...

U Rock !!!