Hi All
Please can you help with the following query to be able to compare data from the current row in a table versus previous row in a table for use in expressions used in a Pivot Table. I have shared an example below with some sample test data that I hope makes sense...
Many thanks in advance for any possible help on this one!
From the example TestData table below I create a Pivot Table where:
Row Dimensions are Regulation,DataType,AssetClass
Column Dimensions are ControlType,Frequency
Possible Pivot Table Filter dimensions are BusinessDate,ControlRunDate,SourceSystem,BookingEntity,ConfigurationFileName as well as the Row Dimensions.
I would then like to calculate two measures (that will be columns in the Pivot Table under the Column dimensions called ControlType,Frequency) called TOTAL and PASS%
These measures would use the following expressions:
Current_TOTAL equals the SUM of ResultCount where RecordStatus in ("MisReported","CorrectReported")
Current_PASS% equals the ((SUM of ResultCount where RecordStatus = "CorrectReported") / Total )*100
I then need to calculate another two expressions that represent the Previous_TOTAL and Previous_PASS% . The calculation is the same as above but is done on the previous record that has the most recent BusinessDate that is less than the BusinessDate on the current record.
Then, in the TOTAL measure use the Current_TOTAL and Previous_TOTAL Expressions to show the Current_TOTAL followed by percentage change of the Current_TOTAL vs Previous TOTAL and ideally include an arrow indicator along with the percent change dependent on whether its a positive or negative number.
In the PASS% measure use the Current_PASS% and Previous_PASS% expressions to show the Current_PASS% followed by percentage change of the Current_PASS% vs PreviousPASS% and ideally include an arrow indicator along with the percent change dependent on whether its a positive or negative number.
I hope that makes sense. thanks again!
Scott
TestData:
Load * Inline [
ControlType,Frequency,RuleType,Regulation,DataType,AssetClass,BusinessDate,ControlRunDate,ConfigurationFileName,SourceSystem,BookingEntity,ResultCount,RecordStatus
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-08-30,2023-09-02,MyConfig1,SYSTEM_X,A,456,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-08-30,2023-09-02,MyConfig1,SYSTEM_X,A,245,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-08-30,2023-09-02,MyConfig1,SYSTEM_X,B,106,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-08-02,2023-08-05,MyConfig1,SYSTEM_X,B,233,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-08-02,2023-08-05,MyConfig1,SYSTEM_X,A,156,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-08-02,2023-08-05,MyConfig1,SYSTEM_X,B,43,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-08-02,2023-08-05,MyConfig1,SYSTEM_X,A,26,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-06-28,2023-07-01,MyConfig1,SYSTEM_X,B,345,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-06-28,2023-07-01,MyConfig1,SYSTEM_X,A,45,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-06-28,2023-07-01,MyConfig1,SYSTEM_X,A,34,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-31,2023-06-03,MyConfig1,SYSTEM_X,A,199,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-31,2023-06-03,MyConfig1,SYSTEM_X,B,456,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-31,2023-06-03,MyConfig1,SYSTEM_X,B,12,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-31,2023-06-03,MyConfig1,SYSTEM_X,A,22,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-03,2023-05-06,MyConfig1,SYSTEM_X,A,103,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-03,2023-05-06,MyConfig1,SYSTEM_X,B,98,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-03,2023-05-06,MyConfig1,SYSTEM_X,B,77,CorrectReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-05-03,2023-05-06,MyConfig1,SYSTEM_X,A,7,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-03-30,2023-04-03,MyConfig1,SYSTEM_X,B,777,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-03-30,2023-04-03,MyConfig1,SYSTEM_X,A,567,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-03-30,2023-04-03,MyConfig1,SYSTEM_X,B,555,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-03-29,2023-04-01,MyConfig1,SYSTEM_X,B,66,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-03-29,2023-04-01,MyConfig1,SYSTEM_X,A,44,MisReported
Accuracy,MTH,Accuracy,CFTC,TRADESTATE,CO,2023-03-29,2023-04-01,MyConfig1,SYSTEM_X,A,34,MisReported
];
