Icon

Financial _​ Estimating Monthly Invoicing from Usage Periods

Financial _ Estimating Monthly Invoicing from Usage Periods

Financial _ Estimating Monthly Invoicing from Usage Periods

Workflow created for the KNIME forum ...

https://forum.knime.com/t/detecting-and-defining-per-month-usage-periods-in-multi-month-data/39149

DESCRIPTION:
The task is to calculate effective monthly billable minutes, from usage periods raw data. The main challlenge resides in the time event structure of the data, that is covering multi-month ranges.

For any comment or bug realted to the workflow, please do not hesitate to leave your comment.


DISCLAIMER:
The sharing of knowledge in KNIME Hub by using these examples and models, have only demonstration purposes in the advantage of the KNIME community.
These are meant to clarify theoretical background of mentioned subjects in caption.
I will not be held responsibility for any damages arising from the use of these models in your investment/valuation related work, without taking formal advise.

Collect and Transform DATA from Repository: Define a CustomReportingGranularity: Monthly Invoicing from Usage Periods' OUTPUT: Billable MonthlyUsage in Time(Minutes): KNIME Forum challenge:Estimating Monthly Invoicing (minutes) from 'Usage Periods' Usage Periods Data fromChallenge: Authentication toGoogle Services/My Drive/KNIME_publicUsage PeriodsDATABilling Periodson Monthly BasisExtract Year and MonthPowered OnReportMerging IDExtract Year and MonthFormat DatesReport Merging IDPowered On_PeriodReport Merging IDPowered Off_PeriodExtract Year and MonthPowered OffAdd Period Merging IDsClean UpFormat DatesStart Loopingon Monthly StepsCollect All PeriodsEstimationsComplete Report(LONG format)Tranform Summary Columns to WIDEValidate Power Event PeriodRanging Within in-loop REPORT_IDType REPORT IDMerge Current in LoopPeriod 'Date&Time' ReferencesIf Invoicing AppliesStarting Monthly EffectiveBillable PeriodIf Invoicing AppliesEnding Monthly EffectiveBillable PeriodBILLABLE MINUTES(seconds)BILLABLE MINUTES(minutes)Clean UpSummary Report(Time-Line format)Upper: Process Power EventsLower: 'null' CaseCreate EmptyRegister for Current REPORT_IDValidate IF 'null'Filter Out 'null' Power Event PeriodREPORT_ID Column... to StringHeaders to LONGExtract Headers for Summary Report Column ResortOnly Headers ColumnRename Column to BindSet upChronologyBind Sorted ClolumnFilter Out 'null' Power Event Period GoogleAuthentication Google DriveConnector CSV Reader CSV Reader Extract Date&TimeFields Math Formula Extract Date&TimeFields String to Date&Time Math Formula Math Formula Extract Date&TimeFields Joiner Column Filter String to Date&Time Table Row ToVariable Loop Start Loop End RowID Pivoting Rule-basedRow Filter ConstantValue Column Joiner Rule Engine Rule Engine Date&TimeDifference Math Formula Column Filter ReferenceColumn Resorter Empty Table Switch Add Empty Rows End IF Missing Value Column Filter Number To String Unpivoting ExtractColumn Header Column Filter Column Rename Sorter Concatenate Missing Value Collect and Transform DATA from Repository: Define a CustomReportingGranularity: Monthly Invoicing from Usage Periods' OUTPUT: Billable MonthlyUsage in Time(Minutes): KNIME Forum challenge:Estimating Monthly Invoicing (minutes) from 'Usage Periods' Usage Periods Data fromChallenge: Authentication toGoogle Services/My Drive/KNIME_publicUsage PeriodsDATABilling Periodson Monthly BasisExtract Year and MonthPowered OnReportMerging IDExtract Year and MonthFormat DatesReport Merging IDPowered On_PeriodReport Merging IDPowered Off_PeriodExtract Year and MonthPowered OffAdd Period Merging IDsClean UpFormat DatesStart Loopingon Monthly StepsCollect All PeriodsEstimationsComplete Report(LONG format)Tranform Summary Columns to WIDEValidate Power Event PeriodRanging Within in-loop REPORT_IDType REPORT IDMerge Current in LoopPeriod 'Date&Time' ReferencesIf Invoicing AppliesStarting Monthly EffectiveBillable PeriodIf Invoicing AppliesEnding Monthly EffectiveBillable PeriodBILLABLE MINUTES(seconds)BILLABLE MINUTES(minutes)Clean UpSummary Report(Time-Line format)Upper: Process Power EventsLower: 'null' CaseCreate EmptyRegister for Current REPORT_IDValidate IF 'null'Filter Out 'null' Power Event PeriodREPORT_ID Column... to StringHeaders to LONGExtract Headers for Summary Report Column ResortOnly Headers ColumnRename Column to BindSet upChronologyBind Sorted ClolumnFilter Out 'null' Power Event Period GoogleAuthentication Google DriveConnector CSV Reader CSV Reader Extract Date&TimeFields Math Formula Extract Date&TimeFields String to Date&Time Math Formula Math Formula Extract Date&TimeFields Joiner Column Filter String to Date&Time Table Row ToVariable Loop Start Loop End RowID Pivoting Rule-basedRow Filter ConstantValue Column Joiner Rule Engine Rule Engine Date&TimeDifference Math Formula Column Filter ReferenceColumn Resorter Empty Table Switch Add Empty Rows End IF Missing Value Column Filter Number To String Unpivoting ExtractColumn Header Column Filter Column Rename Sorter Concatenate Missing Value

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: RE90273
Created at: 2022-01-27
On NodePit since: 2022-02-02
Last update: 2024-04-27
Created with KNIME version: v4.4.2

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!