Dynamic Filtering

KNIME Analytics Platform
1

Hello,
Any help on setting up a dynamic filter is appreciated. I think I am trying to make things harder than necessary due to my limited knowledge of KNIME.

Current data setup is in image below and Excel.

My objective is the filter the data to the previous fiscal month only. Oct = Fiscal 01, Nov = Fiscal 02 etc. In March, I want Fiscal February which is Fiscal Period 05. In April I will retrieve Fiscal Period for calendar month March which is Fiscal 06.

I am deploying to KNIME Server and wish to not manipulate the workflow monthly.

Thank you for any help.

image
image703Ă—439 125 KB

Dynamic Filter Question.xlsx (2.3 MB)

2

Hi @wethernt,
This is possible with KNIME in the following way:

  • Use a Create Date&Time Range node and set it up to create a single row with the starting time as “Current execution time”.
  • You want the previous month, so add a Date&Time Shift node and shift the created date by -1 month.
  • Connect to that a Extract Date&Time Fields node and configure it to extract the month number.
  • Append a Math Formula node to map the month number to your fiscal period. The expression should be: if($Month (number)$ < 10, $Month (number)$ + 3, $Month (number)$ - 9).
  • Now we need to add leading zeros. This can be done with the String Manipulation node and the expression: padLeft(string($fiscal_period$), 2, "0")
  • Now you are ready to filter your table based on this calculation of the value of the previous fiscal month. You can do that using a Reference Row Filter where the top input is your data table and the bottom one is the newly created table with the one row containing the previous fiscal month. Set up the filter to compare the fiscal_period from your data table to the calculated previous fiscal period.

I hope this outline helps!
Kind regards,
Alexander

3 Likes
3

Hi folks,

If you have date column, you can use “Extract Date&Time Fields” node to create the year and month columns and use “rule engine” node to set the period as you need too.

After that, you can use a group by node to make calc and resume your report or “Pivoting” node.

Example attached.

period_Dynamic Filtering.knwf (14.6 KB)

Thanks,

Denis