Previous Month data to Capture

[TE271203]

Hi Team,

I have attached scenario to where I have data across months as Snapshot date and the corresponding aggregate columns.

A, B & E columns are from source, and I am expecting D & F to get derived with Alteryx tools. Can you please assist?

Thank You,

Abdulrasheed

[HomesickSurfer]

Hi @TE271203 

Here's a start...create 2 new Int64 fields with the IF formulas below, replacing '2023' with applicable Date formulas as desired, then reorder fields with the Select tool.

[TE271203]

Thanks for your response. Seems its working for me but if i use it by CORPPART & SNAPSHOT then the summation is not working. What if the scenario is like below. Please assist.

[flying008]

Hi,@TE271203 

Could you please post the data table of input and output ? then we can get what is your want.

[TE271203]

Hi @flying008,

Basically, the data what i have it doesn't hold any key column and the data should be distributed across dates along with other columns from the data applying group by.

Column SNAPSHOT_DATE till PV_IND_DTL should be grouped (as we don't have key) to capture data of all measure columns from CURR_BILLAMT_LOC till RV_PRICE_VARIANCE into new columns as per my previous screen shot.

Please assist.

I am thinking of SQL Query to handle this like

Note: Consider 11/2/2023 as CURRENT_MONTH from SNAPSHOT_DATE column.

SELECT 

RV_PRICE_VARIANCE,

CASE WHEN MONTH([SNAPSHOT_DATE]) = CURRENT_MONTH THEN 0

WHEN MONTH([SNAPSHOT_DATE]) = CURRENT_MONTH + 1 THEN [RV_PRICE_VARIANCE] OVER (PARTITION BY snapshot_date,snapshot_fiscal_month,snapshot_fiscal_month_nm,snapshot_fiscal_year,PROFABBR,PARTKEY,PRODCODE,IC1,IBC2CODE,REGION_CODE,REGION_PARENT,COMPANY_CODE,ISO_COUNTRY,WWACCT8,XCLUSION_CODE,CORPPART,GPL,CBC1CODE,CBC2CODE,COMPETITION_STATUS,PRODUCT_LIFE_CYCLE, PRODUCT_OBJECTIVE, PROFIT_CENTER_GROUP_ID, LEVEL_5_TERRITORY_CDE, PV_IND_DTL ORDER BY SNAPSHOT_DATE) AS Sum_Prv_RV_PRICE_VARIANCE

and so on....

FROM <TABLE>

In above query we took RV_PRICE_VARIANCE as an example but the same should be applied to all measure column in the list.

snapshot_date	snapshot_fiscal_month	snapshot_fiscal_month_nm	snapshot_fiscal_year	PROFABBR	PARTKEY	PRODCODE	IC1	IBC2CODE	REGION_CODE	REGION_PARENT	COMPANY_CODE	ISO_COUNTRY	WWACCT8	EXCLUSION_CODE	CORPPART	GP	CBC1CODE	CBC2CODE	COMPETITION_STATUS	PRODUCT_LIFE_CYCLE	PRODUCT_OBJECTIVE	PROFIT_CENTER_GROUP_ID	LEVEL_5_TERRITORY_CDE	PV_IND_DTL	CURR_BILLAMT_LOC	CURR_COSTAMT_LOC	PRIOR_QUANTITY	PRIOR_BILLAMT_LOC	PRIOR_BILLAMT_DOC	PRIOR_COSTAMT_LOC	PRIOR_COSTAMT_RESTATED	RV_CURRENT_MIX	RV_MIX_VARIANCE	PRIOR_MARGIN_DOC	RV_REVENUE_VARIANCE	RV_MARGIN_VARIANCE	RV_CURR_COMMON_BLAMT	MV_PRICE_VARIANCE	RV_PRICE_VARIANCE
11/2/2023	2	NOVEMBER	2024	CIV	8353294	J53	006	205	0401	0401	0464	IN	0003839	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	101640	PPPV	5936.22	2644.80423	1056	2987.29	3263.92	1491.53267	1557.133526	0	0	0	2627.52	1495.059254	5891.44	253.76	253.76
12/4/2023	3	DECEMBER	2024	CIV	8353294	J53	006	205	0401	0401	0464	IN	0003839	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	101640	PPPV	11357.77	5011.19497	1056	2987.29	3263.92	1491.53267	1557.133526	0	0	0	7898.8	4359.860169	11162.72	480.8	480.8
1/2/2024	4	JANUARY	2024	CIV	8353294	J53	006	205	0401	0401	0464	IN	0003839	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	101640	PPPV	11357.77	5011.19497	1632	4765.26	5044.26	2305.10068	2406.479085	0	0	0	6118.46	3428.865728	11162.72	480.76	480.76
11/2/2023	2	NOVEMBER	2024	CIV	835329	J53	006	205	0002	0002	1082	US	0000124	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	103466	MIX	0	0	1632	5982.5	5982.5	2432.65	2495.166432	0	-5982.5	3549.85	-5982.5	0	0	0	0
12/4/2023	3	DECEMBER	2024	CIV	835329	J53	006	205	0002	0002	1082	US	0000124	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	103466	MIX	0	0	1632	5982.5	5982.5	2432.65	2495.166432	0	-5982.5	3549.85	-5982.5	0	0	0	0
1/2/2024	4	JANUARY	2024	CIV	835329	J53	006	205	0002	0002	1082	US	0000124	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	103466	MIX	0	0	1632	5982.5	5982.5	2432.65	2495.166432	0	-5982.5	3549.85	-5982.5	0	0	0	0
11/2/2023	2	NOVEMBER	2024	CIV	835329	J53	006	074	0442	0442	1473	CN	0034727	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	103263	PPPV	4596.44	2138.17923	1152	4242.86	4242.86	1991.28694	2007.127761	0	0	0	353.58	186.3193533	4596.44	0.008333333	0.008333333
12/4/2023	3	DECEMBER	2024	CIV	835329	J53	006	074	0442	0442	1473	CN	0034727	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	103263	PPPV	4596.44	2138.17923	1152	4242.86	4242.86	1991.28694	2007.127761	0	0	0	353.58	186.3193533	4596.44	0.008333333	0.008333333
1/2/2024	4	JANUARY	2024	CIV	8353294	J539	006	074	0442	0442	1473	CN	0034727	0	1-2112035-x	H9	42000	42001	Slightly Differentiated	Grow	Grow Volume	ICTG	103263	PPPV	5657.16	2631.6051	1152	4242.86	4242.86	1991.28694	2007.127761	0	0	0	1414.3	745.2574131	5657.16	0.013333333	0.013333333