Hi Juna
I would recommend using the CONCAT function in your expression as a variable. The CONCAT function will list all of your years or months distinctly. This can then be used and stored in a variable. The stored variable can then be used in your expression.
You can dynamically select the months or years and the measures in your expression will calcualte accordingly. It all boils down to how you have scripted your data model in relation to your calendar and the data.
Below is an example.
1. First create the distinct list of months and years.
For textual month names use the below expression for your variable.
CONCAT(CHR(39) & DISTINCT MonthName & CHR(39), ',')
This will create a string list like: 'Jan', 'Feb', 'Mar' and so on
When you make a selection for a single month or multiple months, this list will change to whatever is being selected.
For numerical months use the below expression for you variable
CONCAT(DISTINCT Month, ',')
This will create a list like: 1,2,3,4 and so on.
For the year use the below expression for your variable
CONCAT(CHR(39) & DISTINCT Year & CHR(39), ',')
This will create a string list for your year as : '2014', '2015' and so on.
2. Create the variable to be used in your expression
Once you have identified and created your expression you should now create your variable.
Examples:
vMonthList = CONCAT(CHR(39) & DISTINCT MonthName & CHR(39), ',')
vYearList = CONCAT(CHR(39) & DISTINCT Year & CHR(39), ',')
You will need to identify and establish your quarters to years in your Master Calender in QlikView. I assume you will have a calendar table in your script which has the year, month, date and quarter all mapped out.
Using the above technique of listing your years or months, the associated quarter will be highlighted as it will be in your data.
3. Create your expression
Using the variable, you can now include it in your expression for your measures.
Below is an example based on the variables created.
Measures for year
SUM({<Year = {'$(vYearList)'>}Sales)
Measures for Month
SUM({<Month = {'$(vMonthList)@}>Sales)
This can be respectfully used for Quantity measures.
In your chart, when you select a year or multiple years, the chart will show the sales for those selected years. If you used a chart for the quarters then the respectful quarters associated with the selected years will show on the chart.
