Compare values from two different time periods in SAC

[viel]

Hi All,

Good day.

We have a picklist field in the Job Info and we would like to know how many users have been assigned to each picklist value.

We would like confirm with you whether it is possible to compare the values from 2 different time period (e.g., previous date vs. today's date) and calculate the total number of differences in SAC?

for example: we have "Picklist 1" with 12 users as of January 1, 2023 and we want to compare the data from October 20, 2023, which already has 20 users, the table should automatically calculate the counts that as of today's date 8 users have been added to Picklist 1.

In addition, we are currently not using SAC and will only enable it if the desired request is doable. Could you also share additional information/impact of enabling SAC?

Thank you so much.

Regards,

Ronna

[nlgro023]

You mean you are doing this in canvas/ord? If so that would be very difficult. With stories that can be done, difficult formula, but can be done

[viel]

Hi jasper.de.groot ,

We are doing this request in Story Report. However, we are having difficulty setting up the calculations/formula. Could you please share more information on how to set this request in the story report? Thank you so much.

[nlgro023]

Well, you have to make sure the starting entity shows full history, then you need at least 2 calculated columns in such a way like this (the 1st example shows SOP, the 2nd example shows EOP, but hypothetically they could be any given date or timeframe if you adjust it correctly) :

SOP -->
IF(TODATE(CONCAT('01/01/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' )>[Employment#Job Information#Contractual Date] AND NOT(ISNULL([Employment#Job Information#Contractual Date] )),'X',IF([Employment#Job Information#Join (Rejoin) Date]> TODATE(CONCAT('01/01/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' ),'X' , IF(TODATE(CONCAT('01/01/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' )=[Employment#Job Information#Effective Start Date] ,'SOP' ,IF([Employment#Job Information#Effective Start Date] <= TODATE(CONCAT('01/01/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' ) AND [Employment#Job Information#Effective End Date] >= TODATE(CONCAT('01/01/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' ) ,'SOP' , '' ))) )

EOP -->
IF(TODATE(CONCAT('12/31/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' )>[Employment#Job Information#Contractual Date] AND NOT(ISNULL([Employment#Job Information#Contractual Date] )),'X',IF([Employment#Job Information#Join (Rejoin) Date]> TODATE(CONCAT('12/31/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' ),'X' , IF(TODATE(CONCAT('12/31/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' )=[Employment#Job Information#Effective Start Date] ,'SOP' ,IF([Employment#Job Information#Effective Start Date] <= TODATE(CONCAT('12/31/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' ) AND [Employment#Job Information#Effective End Date] >= TODATE(CONCAT('12/31/' ,TOTEXT( YEAR(CURRENTDATE())) ),'MM/dd/yyyy' ) ,'SOP' , '' ))) )

In the story itself you then use an aggregation with a specific condition (in this case 1 for SOP and 1 for EOP) to draw the right results and then a final calculated measure to deduct the 2. This is rather advanced stuff, hence if this info doesn't suffice for you, you may want to get someone in to help with setting such a thing up.