Extracting the YTD of same period last year

[tetew89]

Hi, I have a table with following fields:  ,

  CODE3
, SOUnits
, SOValues
, STUnits,
, SOUnits_YTD

, Month

, year

And I have created this script on top of other temporary tables to get the YTD SOUnits: 

tmp_b:
Load
*,
if(Year = previous(Year) and CODE3 = previous(CODE3),
RangeSum(peek(SOUnits_YTD),SOUnits),
SOUnits) as SOUnits_YTD
resident tmp_a
order by CODE3, Year, Month;

It is working, but now I need to create a new column that shows the YTD of the same period last year. So if I select April 2023, SOUnits_YTD would give me the aggregate of Jan 2023 - April 2023. The new column should give the aggregate of SOUnits from Jan 2022 until April 2022. 

Could you please help me?

[Oleg_Troyansky]

HI @tetew89 ,

I could recommend 2 possible ways to solve this problem:

1. If you'd like to continue calculating YTD values in the script, you could add another resident reload and load the same accumulated YTD values with The Year value increased by 1. So, for example, YTD total for April 2022 will get loaded as "Prior YTD" total for April 2023. Then, join this data back to your main table, adding the new "Prior YTD" field there. Something along these lines:

Left Join (tmp_b)

LOAD

     <all main keys for the entity>,

     Year + 1 as Year,

     Month,

     SOUnits_YTD as SOUnits_PYTD
resident

     tmp_b;

This approach is easier, but could be wasteful if you have a lot of data.

2. The other approach is more scalable, I believe, and more "professional", but it involves quite a bit more work:

   - you don't need to aggregate YTD values as you do now

   - You generate a Date field out of your Year and Month and create a traditional Calendar table

   - Then you create an "AS of Date" table that connects your "presentation date" with your "transaction date", and in that table, you can define two conditional flags - the current YTD flag and the prior YTD flag. In addition, you will need the "Current Month" flag for those entries when the two dates are equal.
   - in the UI, you can condition all your aggregations with one of these conditional flags.

You need to look up "As of Date table" in one of the blogs - for example, this article in my blog:

How to Use the As of Date Table 

Cheers,