community Alteryx IO MyAlteryx
alteryx Community
Search
This Board
  • Global Community
  • This Category
  • This Board
  • Knowledge
  • Users
 cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Last twelve month calculation under irregular timeseries and various scenarios

onanm
6 - Meteoroid
‎03-29-2021 02:11 PM

Hi community,

 

I need to calculate last twelve month value of a measure given some conditions. But this calculation should be added as a row with a new dimension member of LTM. Below picture simply illustrates the calculation logic. For Quarter case LTM equals 'latest quarter value' + 'latest year value' - 'previous year quarter value'. In other words 3m + 12m - 3m. For half-year case LTM equals 'latest half-year value' + 'latest year value' - 'previous year half-year value'. In other words 6m + 12m - 6m

 

onanm_0-1617049568259.png

 

But in order to calculate LTM, multiple conditions need to be satisfied. There must be previous period in last year value and last full year value. On top of that, all these values should be in same currency. The challenge is that data is not regular as I do not have always quarter, half-year, year, etc. records. It changes from company to company.

 

I created below sample input data. My data initially does not include these NULL months. I create those to facilitate rolling if applicable. Let me further clarify case by case:

 

  • CompanyA June 2018 period is simple to implement. LTM can be calculated for June 2018 as June 2017 half-year and December 2017 full-year is available in USD terms. LTM sales value is June 2018 + December 2017 - June 2017. Please note that I do not need LTM Sales as a new column. Instead, I need as a new row where Period is LTM.
  • But, CompanyA March 2019 period is not ideal to calculate LTM as same period in last year (March 2018) is missing. Whereas, June 2019 period satisfies LTM conditions.
  • Please also note that December is not always 'Year'. The data should not be taken into account as calendar based. Some companies have year-end a.k.a 'Year' other than December. As you can see CompanyB case. year-end is June and so half-year is December. However, December 2018 period does not satisfy LTM condition due to no previous period.
  • CompanyD June 2019 period is suitable to calculate LTM. However, June 2020 is not ideal to calculate. Although this period satisfies required periods conditions, it fails to satisfy currency condition, as currency should be same for all periods subject to LTM.
  • CompanyE March 2018, June 2018, and September 2018 are ideal to calculate LTM. However September 2018 is tricky here. As this period is Q3 and covers only three months. So, in this case LTM = Q3 + H1 + Year - Q3 - H1
  • September 2019 is again tricky. It already covers 9 months, so I need 9 months coverage in previous year. In this case LTM = YTD + Year - Q3 - H1

 

As you can see the data is quite irregular:

 

  • Not all companies have December year-end (Year)
  • Interims are not stable. Q1, Q2, and Q3 as quarters (3months range) H1 as half year (6months range), YTD (9months range)
  • Currency can be changing across periods
CompanyDatePeriodFXSales
CompanyA31.12.2016YearUSD1308
CompanyA31.01.2017   
CompanyA28.02.2017   
CompanyA31.03.2017   
CompanyA30.04.2017   
CompanyA31.05.2017   
CompanyA30.06.2017H1USD1767
CompanyA31.07.2017   
CompanyA31.08.2017   
CompanyA30.09.2017   
CompanyA31.10.2017   
CompanyA30.11.2017   
CompanyA31.12.2017YearUSD3680
CompanyA31.01.2018   
CompanyA28.02.2018   
CompanyA31.03.2018   
CompanyA30.04.2018   
CompanyA31.05.2018   
CompanyA30.06.2018H1USD1892
CompanyA31.07.2018   
CompanyA31.08.2018   
CompanyA30.09.2018   
CompanyA31.10.2018   
CompanyA30.11.2018   
CompanyA31.12.2018YearUSD4100
CompanyA31.01.2019   
CompanyA28.02.2019   
CompanyA31.03.2019Q1USD2823
CompanyA30.04.2019   
CompanyA31.05.2019   
CompanyA30.06.2019H1USD8511
CompanyA31.07.2019   
CompanyA31.08.2019   
CompanyA30.09.2019   
CompanyA31.10.2019   
CompanyA30.11.2019   
CompanyA31.12.2019YearUSD6494
CompanyA31.01.2020   
CompanyA29.02.2020   
CompanyA31.03.2020   
CompanyA30.04.2020   
CompanyA31.05.2020   
CompanyA30.06.2020H1USD3714
CompanyB30.06.2017YearEUR165
CompanyB31.07.2017   
CompanyB31.08.2017   
CompanyB30.09.2017   
CompanyB31.10.2017   
CompanyB30.11.2017   
CompanyB31.12.2017   
CompanyB31.01.2018   
CompanyB28.02.2018   
CompanyB31.03.2018   
CompanyB30.04.2018   
CompanyB31.05.2018   
CompanyB30.06.2018YearEUR2242
CompanyB31.07.2018   
CompanyB31.08.2018   
CompanyB30.09.2018   
CompanyB31.10.2018   
CompanyB30.11.2018   
CompanyB31.12.2018H1EUR3019
CompanyB31.01.2019   
CompanyB28.02.2019   
CompanyB31.03.2019   
CompanyB30.04.2019   
CompanyB31.05.2019   
CompanyB30.06.2019YearEUR5940
CompanyC30.06.2020H1EUR140
CompanyC31.07.2020   
CompanyC31.08.2020   
CompanyC30.09.2020Q3EUR85
CompanyD30.06.2018H1USD798
CompanyD31.07.2018   
CompanyD31.08.2018   
CompanyD30.09.2018   
CompanyD31.10.2018   
CompanyD30.11.2018   
CompanyD31.12.2018YearUSD2056
CompanyD31.01.2019   
CompanyD28.02.2019   
CompanyD31.03.2019   
CompanyD30.04.2019   
CompanyD31.05.2019   
CompanyD30.06.2019H1USD2200
CompanyD31.07.2019   
CompanyD31.08.2019   
CompanyD30.09.2019   
CompanyD31.10.2019   
CompanyD30.11.2019   
CompanyD31.12.2019YearEUR4778
CompanyD31.01.2020   
CompanyD29.02.2020   
CompanyD31.03.2020   
CompanyD30.04.2020   
CompanyD31.05.2020   
CompanyD30.06.2020H1USD2494
CompanyE31.12.2016YearEUR4591
CompanyE31.01.2017   
CompanyE28.02.2017   
CompanyE31.03.2017Q1EUR1214
CompanyE30.04.2017   
CompanyE31.05.2017   
CompanyE30.06.2017H1EUR2493
CompanyE31.07.2017   
CompanyE31.08.2017   
CompanyE30.09.2017Q3EUR1333
CompanyE31.10.2017   
CompanyE30.11.2017   
CompanyE31.12.2017YearEUR5177
CompanyE31.01.2018   
CompanyE28.02.2018   
CompanyE31.03.2018Q1EUR1406
CompanyE30.04.2018   
CompanyE31.05.2018   
CompanyE30.06.2018H1EUR2855
CompanyE31.07.2018   
CompanyE31.08.2018   
CompanyE30.09.2018Q3EUR1546
CompanyE31.10.2018   
CompanyE30.11.2018   
CompanyE31.12.2018YearEUR6357
CompanyE31.01.2019   
CompanyE28.02.2019   
CompanyE31.03.2019   
CompanyE30.04.2019   
CompanyE31.05.2019   
CompanyE30.06.2019   
CompanyE31.07.2019   
CompanyE31.08.2019   
CompanyE30.09.2019YTDEUR5419
CompanyE31.10.2019   
CompanyE30.11.2019   
CompanyE31.12.2019YearEUR7419
CompanyF31.12.2018YearGBP5299
CompanyF31.01.2019   
CompanyF28.02.2019   
CompanyF31.03.2019Q1GBP1398
CompanyF30.04.2019   
CompanyF31.05.2019   
CompanyF30.06.2019H1GBP2638
CompanyF31.07.2019   
CompanyF31.08.2019   
CompanyF30.09.2019YTDGBP3849
CompanyF31.10.2019   
CompanyF30.11.2019   
CompanyF31.12.2019YearGBP5332
CompanyF31.01.2020   
CompanyF29.02.2020   
CompanyF31.03.2020Q1GBP1970
CompanyF30.04.2020   
CompanyF31.05.2020   
CompanyF30.06.2020H1GBP4397
CompanyF31.07.2020   
CompanyF31.08.2020   
CompanyF30.09.2020YTDGBP5661

 

Desired output to be added back to original data:

 

CompanyDatePeriodFXSales
CompanyA30.06.2018LTMUSD3805
CompanyA30.06.2019LTMUSD1697
CompanyD30.06.2019LTMUSD3458
CompanyE31.03.2018LTMEUR5369
CompanyE30.06.2018LTMEUR5539
CompanyE30.09.2018LTMEUR5752
CompanyE30.09.2019LTMEUR7375
CompanyF31.03.2020LTMGBP5904
CompanyF30.06.2020LTMGBP7091
CompanyF30.09.2020LTMGBP7144

 

I think it requires Batch Macro kind of approach. I am quite new and not much sophisticated to be honest. Any guidance would be deeply appreciated.

 

Thanks a lot

Reply
0 Likes
1 REPLY 1
BenMoss
ACE Emeritus
ACE Emeritus
‎03-30-2021 11:18 PM

Hi @onanm it seems like the community was busy yesterday and your post slipped through the net, I imagine this may be down to the complexity of the problem and the time it may take someone to jump in and help.

 

I think we can potentially make your problem a bit clearer which may encourage more people to take a look. Specifically, it may be helpful to add, into your desired output table, three new columns, one containing the calculation used in it's raw form (Q1+H1....), one containing the values that translates to (1+4-3...) and a third one, which indicates the row numbers from which that information was gathered.

 

This may make your problem just a bit easier to understand.

 

I don't think this is a batch macro problem though, so I wouldn't go looking down that hole (I mean you can and it may provide you with a solution, but it would be an unnecessary detour).

 

Ben

Reply
0 Likes
view community events
inspire session catalog
Labels
Top Solution Authors
View All