I have data with a bunch of columns but two of the columns are YEAR and MONTH. I would like to pivot/group but instead of a simple 4 new columns with the revenue summed for 2018, 2019, 2020, 2021…I would like 5 columns… 2018, 2019, 2020, 2020 (jan-aug), 2021. What is the best way to do that? I have tried a few diff things but nothing is working quite like I want. Hoping you all can help!
Hi @ahortonmilsig , can you show us some sample data and what the expected results would be the sample data?
And may be share your workflow to see what you have done so far, so we can point you in the right direction.
Before pivoting, I would probably suggest adding an additional column, based on the month that specifies whether the revenue should be attributed to YTD or FY. (You can use the Rule Engine node for this.) After that, use the Pivoting node to pivot, and finally, the column aggregator to properly sum up the correct columns…
1 Like
Thank you for the info @ahortonmilsig .
Can you explain how do you define “FY” vs “YTD”?
Also, how are you grouping by? Material, Bus Unit, Account Name, Source, PO Type, Region? Could you have Apple with PO Type Web and also Offline? If that were the case, how would the expected results look like for Apple? Would it be 2 records with 2 different types?
Similarly for Region, what if you had these:
Apple, Web, USA
Apple, Offline, USA
Apple, Web, APAC
Apple, Offline, APAC
How would the grouping be? 4 different rows?
Of course, add Source also in there, and you get a whole lot other combinations.
That’s what I initially tried to do but couldn’t figure out how to have revenue count towards two columns. For example… Jan-Aug 2020 will need to go in/count towards the 2020 YoY (YTD) column and also in the 2020 FY column along with Sep-Dec 2020 revenue
Full Year is FY, YTD is Year to Date
2020 FY = 2020 Full Year Revenue (Jan thru Dec 2020)
2020 YTD = 2020 YTD Revenue (Jan thru Aug 2020)
2021 YTD = 2021 YTD Revenue (Jan thru Aug 2020)
Yes, I could have various results but the accounts will only show up for one region but could have the different PO types. It would essentially be …What was the revenue for the diff years for that material at that account for that PO type.
Hi @ahortonmilsig,
you have two different calculations (YTD and FY) therefore you need 2 subflows
a) for YTD: a filter node which only includes Jan thru Aug Data and define @Snowy mentioned an additional column which contains the expected column header after pivoting (e.g. 2020 YTD Revenue).
b) for FY: as @Snowy mentioned an additional column which contains the expected column header after pivoting (e.g. 2020 FY Revenue).
then combine both subflows with the concatenate node and pivot your data.
BR
2 Likes
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.