Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Filter by Range using beginning and ending value

Hi,

I want to create a range filter where users can select the beginning year (from drop down menu) and ending year (from drop down menu) and get revenue for those years.

For example, data includes 2015 through 2019. But a user may want to select data from 2016 to 2018 and therefore input those years as beginning and ending years.

Is this possible?

Note: I am aware that I can create a list box but I am not looking for that.

Thanks,

CD

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

You can create 2 variables like FromYear and ToYear. And use this in Set analysis in Expression to filter only required year.

Set analysis will look like below.

Sum({<Year = {">=$(vFromYear)<=$(vToYear)"}>}Sales)

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Contributor III
Contributor III

Thanks, Kaushik. I am rookie on set analysis. Can you pls attach a QV example?

Thanks,

CD

Highlighted
Digital Support
Digital Support

I would start with the following Design Blog post and potentially review Help too and I will include the base Design Blog URL in case you want to look over the other Set Analysis related posts out there, I just am providing the starting one here...

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Contributor
Contributor

Creating a Date Range Filter That Automatically Updates Step 1: Create Start and End Date Parameters Right-click in the Data window and then select Create Parameter. In the Create Parameter dialog box, do the following, and then click OK: Name the parameter. In the example workbook, the parameter is named Start date. For Data Type, select Date. For Allowable values, select All. Right-click the parameter and then select Show Parameter Control. Repeat steps 1-3 to create an End date parameter. Step 2: Create a Parameter to Control a Filter Right-click in the Data window and then select Create Parameter. In the Create Parameter dialog box, do the following, and then click OK: Name the parameter. In the example workbook, the parameter is named Filter ON/OFF. For Data Type, select String. For Allowable values, select List. In List of values, add ON and OFF. Right-click the parameter and then select Show Parameter Control. Step 3: Create a Calculated Field to Use as a Filter Select Analysis > Create Calculated Field. In the Calculated Field dialog box that opens, do the following and then click OK: Name the calculated field. In the example workbook, the calculated field is named Filtered date. In the formula field, create a calculated field similar to the following: IF [Filter ON/OFF]="OFF" THEN [date] ELSEIF [date]<[End date] AND [date]>[Start date] THEN [date] END Drag the newly created calculated field from the Dimensions pane to the Filter shelf. In the Filter dialog box that opens, select Months, and then click Next. Select Exclude and Null, and then click OK. Step 4: Create the View Drag Filtered date to Columns. Drag Filtered date to Columns again, right-click Filtered date on Columns, and then select Month. Drag amount to Rows. Drag type to Color. For Marks, select Bar. Thanks PrepaidCardStatus