Icon

Just KNIME It S02 _​ CH10 _​ Calculating YTD and MTD

Restaurant Yummy records its sales values on a daily basis. The data contains two columns: Date and Sales.

Year-to-Date (YTD) Sales and Month-to-Date (MTD) Sales are important metrics to track the revenue of the business. But what exactly are YTD and MTD values? The YTD value allows you to calculate the metric (e.g., sum of sales) for the current year, while the MTD value denotes a metric value for the current month.

You are asked to build a KNIME Workflow that takes this data as input and adds YTD and MTD values across each record.

Author: @mpattadkal

Just KNIME It - Season2 - Calculating YTD and MTD for Restaurant YummyDescription: Restaurant Yummy records its sales values on a daily basis. The data contains two columns: Date and Sales. Year-to-Date (YTD) Sales and Month-to-Date (MTD) Sales are important metrics to track the revenue of the business. But what exactly are YTD and MTD values? The YTD value allows you to calculate the metric (e.g., sum of sales) for the current year, while the MTD value denotes a metric value for thecurrent month. You are asked to build a KNIME Workflow that takes this data as input and adds YTD and MTD values across each record. Author: @mpattadkal MTD and YTD:@Gordon.Data's proposed solution result Sales Data.xlsx https://hub.knime.com/-/spaces/-/latest/~CIELpKr1w66A2rLz/QC sort by date ascendingformat datesquery: month number, year numbercumulative sales aggregation for the whole rangecumulative Monthly_Salesmerge monthly lagged(+1) cumulativecumulative Yearly_Salesrename cum_Month_Salesrename cum_Year_Salesmerge yearly lagged(+1) cumulative MTD_sales filin emptyes with zeroesYTD_salesdata frame column clean-uptime range starting 2021-01-01sum aggregate monthly cummulativelag yearly sales (-1)lag monthly sales (-1)sum aggregate monthly cummulativeKNIME component, with a visualization tackling MTD comparisons ...R packages: install.packages("ggplot2") Excel Reader Sorter String to Date&Time Extract Date&TimeFields Moving Aggregation GroupBy Joiner GroupBy Column Rename(Regex) Column Rename(Regex) Joiner Math Formula Missing Value Math Formula Column Filter Testing Data Moving Aggregation Lag Column Lag Column Moving Aggregation ComparingMonthly Sales Just KNIME It - Season2 - Calculating YTD and MTD for Restaurant YummyDescription: Restaurant Yummy records its sales values on a daily basis. The data contains two columns: Date and Sales. Year-to-Date (YTD) Sales and Month-to-Date (MTD) Sales are important metrics to track the revenue of the business. But what exactly are YTD and MTD values? The YTD value allows you to calculate the metric (e.g., sum of sales) for the current year, while the MTD value denotes a metric value for thecurrent month. You are asked to build a KNIME Workflow that takes this data as input and adds YTD and MTD values across each record. Author: @mpattadkal MTD and YTD:@Gordon.Data's proposed solution result Sales Data.xlsx https://hub.knime.com/-/spaces/-/latest/~CIELpKr1w66A2rLz/QC sort by date ascendingformat datesquery: month number, year numbercumulative sales aggregation for the whole rangecumulative Monthly_Salesmerge monthly lagged(+1) cumulativecumulative Yearly_Salesrename cum_Month_Salesrename cum_Year_Salesmerge yearly lagged(+1) cumulative MTD_sales filin emptyes with zeroesYTD_salesdata frame column clean-uptime range starting 2021-01-01sum aggregate monthly cummulativelag yearly sales (-1)lag monthly sales (-1)sum aggregate monthly cummulativeKNIME component, with a visualization tackling MTD comparisons ...R packages: install.packages("ggplot2") Excel Reader Sorter String to Date&Time Extract Date&TimeFields Moving Aggregation GroupBy Joiner GroupBy Column Rename(Regex) Column Rename(Regex) Joiner Math Formula Missing Value Math Formula Column Filter Testing Data Moving Aggregation Lag Column Lag Column Moving Aggregation ComparingMonthly Sales

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: gabeg
Created at: 2023-06-02
On NodePit since: 2023-06-02
Last update: 2024-11-04
Created with KNIME version: v4.7.1
Tags: JKISeason2-10

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

Try NodePit Runner!