calculate_bi_marketing_md Normal / ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID TRANSNAME Y TRANSNAME STATUS Y STATUS LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS STARTDATE Y STARTDATE ENDDATE Y ENDDATE LOGDATE Y LOGDATE DEPDATE Y DEPDATE REPLAYDATE Y REPLAYDATE LOG_FIELD Y LOG_FIELD EXECUTING_SERVER N EXECUTING_SERVER EXECUTING_USER N EXECUTING_USER CLIENT N CLIENT ID_BATCH Y ID_BATCH SEQ_NR Y SEQ_NR LOGDATE Y LOGDATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS INPUT_BUFFER_ROWS Y INPUT_BUFFER_ROWS OUTPUT_BUFFER_ROWS Y OUTPUT_BUFFER_ROWS ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE LOGGING_OBJECT_TYPE Y LOGGING_OBJECT_TYPE OBJECT_NAME Y OBJECT_NAME OBJECT_COPY Y OBJECT_COPY REPOSITORY_DIRECTORY Y REPOSITORY_DIRECTORY FILENAME Y FILENAME OBJECT_ID Y OBJECT_ID OBJECT_REVISION Y OBJECT_REVISION PARENT_CHANNEL_ID Y PARENT_CHANNEL_ID ROOT_CHANNEL_ID Y ROOT_CHANNEL_ID ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS LOG_FIELD N LOG_FIELD ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE METRICS_DATE Y METRICS_DATE METRICS_CODE Y METRICS_CODE METRICS_DESCRIPTION Y METRICS_DESCRIPTION METRICS_SUBJECT Y METRICS_SUBJECT METRICS_TYPE Y METRICS_TYPE METRICS_VALUE Y METRICS_VALUE 0.0 0.0 10000 50 50 N Y 50000 Y N 1000 100 - 2025/05/13 13:45:32.351 - 2025/05/13 13:45:32.351 H4sIAAAAAAAAAAMAAAAAAAAAAAA= N dw bi indoteknik localhost POSTGRESQL Native dw_bi_indoteknik 5703 metabase Encrypted 2be98dfba28d7bf86853c9f58cfc4f889 FORCE_IDENTIFIERS_TO_LOWERCASE N FORCE_IDENTIFIERS_TO_UPPERCASE N IS_CLUSTERED N PORT_NUMBER 5703 PRESERVE_RESERVED_WORD_CASE Y QUOTE_ALL_FIELDS N SUPPORTS_BOOLEAN_DATA_TYPE Y SUPPORTS_TIMESTAMP_DATA_TYPE Y USE_POOLING N erp indoteknik production localhost POSTGRESQL Native erp_indoteknik 5901 odoo Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 FORCE_IDENTIFIERS_TO_LOWERCASE N FORCE_IDENTIFIERS_TO_UPPERCASE N IS_CLUSTERED N PORT_NUMBER 5901 PRESERVE_RESERVED_WORD_CASE Y QUOTE_ALL_FIELDS N SUPPORTS_BOOLEAN_DATA_TYPE Y SUPPORTS_TIMESTAMP_DATA_TYPE Y USE_POOLING N parent category Sort rows Y last year Sort rows 2 Y Sort rows Merge join Y Sort rows 2 Merge join Y this year Sort rows 3 Y Merge join Merge join 2 Y Sort rows 3 Merge join 2 Y category target Sort rows 4 Y Merge join 2 Merge join 3 Y Sort rows 4 Merge join 3 Y trx last year Sort rows 5 Y trx this year Sort rows 6 Y Merge join 3 Merge join 4 Y Sort rows 5 Merge join 4 Y Merge join 4 Merge join 5 Y Sort rows 6 Merge join 5 Y Select values Calculator Y Calculator Select values 2 Y last year full Sort rows 7 Y Merge join 5 Merge join 6 Y Sort rows 7 Merge join 6 Y Merge join 6 Select values Y Select values 2 Insert / update Y Calculator Calculator Y 1 none Y diff_ly SUBTRACT this_year last_year Number -1 -1 N growth_value DIVIDE diff_ly last_year Number -1 -1 N diff_target SUBTRACT this_year target Number -1 -1 N achv_target DIVIDE diff_target target Number -1 -1 N diff_trx SUBTRACT trx_this_year trx_last_year Number -1 -1 N growth_trx DIVIDE diff_trx trx_last_year Number -1 -1 N diff_ly_full SUBTRACT this_year last_year_full Number -1 -1 N growth_value_full DIVIDE diff_ly_full last_year_full Number -1 -1 N

864 384 Y Insert / update InsertUpdate Y 1 none dw bi indoteknik 100 N public t_omset_by_parent_category id id = id id N category category Y history_date history_date Y last_year last_year Y this_year this_year Y target target Y trx_last_year trx_last_year Y trx_this_year trx_this_year Y growth_value growth_value Y achv_target achv_target Y growth_trx growth_trx Y growth_value_full growth_value_full Y

624 384 Y Merge join MergeJoin Y 1 none LEFT OUTER Sort rows Sort rows 2 id product_category_id

352 288 Y Merge join 2 MergeJoin Y 1 none LEFT OUTER Merge join Sort rows 3 id product_category_id

496 288 Y Merge join 3 MergeJoin Y 1 none LEFT OUTER Merge join 2 Sort rows 4 id id

624 288 Y Merge join 4 MergeJoin Y 1 none LEFT OUTER Merge join 3 Sort rows 5 id product_category_id

752 288 Y Merge join 5 MergeJoin N 1 none LEFT OUTER Merge join 4 Sort rows 6 id product_category_id

864 288 Y Merge join 6 MergeJoin Y 1 none LEFT OUTER Merge join 5 Sort rows 7 id product_category_id

992 288 Y Select values SelectValues Y 1 none id category history_date last_year this_year target trx_last_year trx_this_year last_year_full N

992 384 Y Select values 2 SelectValues Y 1 none id category history_date last_year this_year target trx_last_year trx_this_year growth_value achv_target growth_trx growth_value_full N

752 384 Y Sort rows SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N id Y N N 0 N

256 288 Y Sort rows 2 SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N product_category_id Y N N 0 N

352 208 Y Sort rows 3 SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N product_category_id Y N N 0 N

496 208 Y Sort rows 4 SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N id Y N N 0 N

624 208 Y Sort rows 5 SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N product_category_id Y N N 0 N

752 208 Y Sort rows 6 SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N product_category_id Y N N 0 N

864 208 Y Sort rows 7 SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N product_category_id Y N N 0 N

992 208 Y category target TableInput Y 1 none dw bi indoteknik select * from category_target 0 N N N N Integer normal id 9 0 Table input id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N String normal name 2147483647 -1 Table input name . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal target -1 -1 Table input target ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N

624 112 Y last year TableInput Y 1 none dw bi indoteknik select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id where 1=1 and aml.parent_state = 'posted' and am.move_type = 'out_invoice' and aml.date between to_timestamp(get_first_day_last_year(), 'YYYY-MM-DD') and to_timestamp(get_now_last_year(), 'YYYY-MM-DD') group by p.parent_category_id 0 N N N N Integer normal product_category_id 9 0 last year product_category_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal last_year -1 -1 last year last_year ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N

352 112 Y last year full TableInput Y 1 none dw bi indoteknik select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year_full from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id where 1=1 and aml.parent_state = 'posted' and am.move_type = 'out_invoice' and to_char(aml.date, 'YYYY') = get_last_year() group by p.parent_category_id 0 N N N N Integer normal product_category_id 9 0 last year full product_category_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal last_year_full -1 -1 last year full last_year_full ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N

992 112 Y parent category TableInput Y 1 none erp indoteknik production select id, category, now()::timestamp-'7 hours'::interval as history_date from( select ppc.id, name as category from product_public_category ppc where ppc.id in( 2040, 2097, 2161, 2222, 2246, 2273, 2315, 2354, 2394, 2420, 2477, 2445 ) union select 999999, 'Others' )a order by id 0 N N N N Integer normal id 9 0 parent category id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N String normal category 2147483647 -1 parent category category . , none N Y 0 N N N en_US Asia/Bangkok N

160 288 Y this year TableInput Y 1 none dw bi indoteknik select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as this_year from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id where 1=1 and aml.parent_state = 'posted' and am.move_type = 'out_invoice' and to_char(aml.date, 'YYYY') = get_now_year() group by p.parent_category_id 0 N N N N Integer normal product_category_id 9 0 this year product_category_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal this_year -1 -1 this year this_year ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N

496 112 Y trx last year TableInput Y 1 none dw bi indoteknik select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_last_year from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id where 1=1 and aml.parent_state = 'posted' and am.move_type = 'out_invoice' and aml.date between to_timestamp(get_first_day_last_year(), 'YYYY-MM-DD') and to_timestamp(get_now_last_year(), 'YYYY-MM-DD') group by p.parent_category_id 0 N N N N Integer normal product_category_id 9 0 trx last year product_category_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal trx_last_year 15 0 trx last year trx_last_year ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

752 112 Y trx this year TableInput Y 1 none dw bi indoteknik select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_this_year from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id where 1=1 and aml.parent_state = 'posted' and am.move_type = 'out_invoice' and to_char(aml.date, 'YYYY') = get_now_year() group by p.parent_category_id 0 N N N N Integer normal product_category_id 9 0 trx this year product_category_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal trx_this_year 15 0 trx this year trx_this_year ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

864 112 Y N