Previous Month data to Capture

Question

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

TE271203 · Answer

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_datesnapshot_fiscal_monthsnapshot_fiscal_month_nmsnapshot_fiscal_yearPROFABBRPARTKEYPRODCODEIC1IBC2CODEREGION_CODEREGION_PARENTCOMPANY_CODEISO_COUNTRYWWACCT8EXCLUSION_CODECORPPARTGPCBC1CODECBC2CODECOMPETITION_STATUSPRODUCT_LIFE_CYCLEPRODUCT_OBJECTIVEPROFIT_CENTER_GROUP_IDLEVEL_5_TERRITORY_CDEPV_IND_DTLCURR_BILLAMT_LOCCURR_COSTAMT_LOCPRIOR_QUANTITYPRIOR_BILLAMT_LOCPRIOR_BILLAMT_DOCPRIOR_COSTAMT_LOCPRIOR_COSTAMT_RESTATEDRV_CURRENT_MIXRV_MIX_VARIANCEPRIOR_MARGIN_DOCRV_REVENUE_VARIANCERV_MARGIN_VARIANCERV_CURR_COMMON_BLAMTMV_PRICE_VARIANCERV_PRICE_VARIANCE11/2/20232NOVEMBER2024CIV8353294J53006205040104010464IN000383901-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG101640PPPV5936.222644.8042310562987.293263.921491.532671557.1335260002627.521495.0592545891.44253.76253.7612/4/20233DECEMBER2024CIV8353294J53006205040104010464IN000383901-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG101640PPPV11357.775011.1949710562987.293263.921491.532671557.1335260007898.84359.86016911162.72480.8480.81/2/20244JANUARY2024CIV8353294J53006205040104010464IN000383901-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG101640PPPV11357.775011.1949716324765.265044.262305.100682406.4790850006118.463428.86572811162.72480.76480.7611/2/20232NOVEMBER2024CIV835329J53006205000200021082US000012401-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG103466MIX0016325982.55982.52432.652495.1664320-5982.53549.85-5982.5000012/4/20233DECEMBER2024CIV835329J53006205000200021082US000012401-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG103466MIX0016325982.55982.52432.652495.1664320-5982.53549.85-5982.500001/2/20244JANUARY2024CIV835329J53006205000200021082US000012401-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG103466MIX0016325982.55982.52432.652495.1664320-5982.53549.85-5982.5000011/2/20232NOVEMBER2024CIV835329J53006074044204421473CN003472701-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG103263PPPV4596.442138.1792311524242.864242.861991.286942007.127761000353.58186.31935334596.440.0083333330.00833333312/4/20233DECEMBER2024CIV835329J53006074044204421473CN003472701-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG103263PPPV4596.442138.1792311524242.864242.861991.286942007.127761000353.58186.31935334596.440.0083333330.0083333331/2/20244JANUARY2024CIV8353294J539006074044204421473CN003472701-2112035-xH94200042001Slightly DifferentiatedGrowGrow VolumeICTG103263PPPV5657.162631.605111524242.864242.861991.286942007.1277610001414.3745.25741315657.160.0133333330.013333333

flying008 · Answer

Hi,@TE271203

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

TE271203 · Answer

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.