cancel
Showing results for 
Search instead for 
Did you mean: 

Compare values from two different time periods in SAC

viel
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

nlgro023
Active Contributor
0 Kudos

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
Participant
0 Kudos

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
Active Contributor
0 Kudos

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.