topic Re: Filter by Range using beginning and ending value in QlikView

Filter by Range using beginning and ending value

chiranjivdas — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Filter by Range using beginning and ending value

kaushiknsolanki — Sat, 22 Feb 2020 13:25:30 GMT

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)

Re: Filter by Range using beginning and ending value

chiranjivdas — Sat, 22 Feb 2020 20:07:41 GMT

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

Thanks,

Re: Filter by Range using beginning and ending value

Brett_Bleess — Wed, 25 Mar 2020 21:22:13 GMT

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/SetAnalysis/set-analysis-expressions.htm

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

Regards,
Brett

Re: Filter by Range using beginning and ending value

joans123 — Sat, 28 Mar 2020 04:03:25 GMT

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