Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- QlikSense Pivot Table Expressions - Calculate Perc...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Scott2

Contributor III

2023-09-06
01:13 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

QlikSense Pivot Table Expressions - Calculate Percentage Change between Current and Previous Totals

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

];

0 Replies