Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (1)
1 Solution

Accepted Solutions
deepakk
Valued Contributor III

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

hi

Try this code.

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



9 Replies
MVP
MVP

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

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

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

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.

MVP
MVP

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

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

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

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

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

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

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

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

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

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
Valued Contributor III

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

hi

Try this code.

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



Not applicable

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

Thanks a lot Deepak...

U Rock !!!

Community Browser