topic Re: Using Aggr with a Pivot Table to populate a row of cells with the MAX Row Value in App Development

Using Aggr with a Pivot Table to populate a row of cells with the MAX Row Value

stevenoga1 — Sat, 16 Nov 2024 05:07:44 GMT

Using a filtered set of values from the app....

I have a Pivot Table of values that appears as below when I set MEASURES to SUM(Case_CNT).

The Dimensions are STANDARD_EVENT and OPEN_DATE

Standard_Event	7/14/19	7/13/19	7/12/19	7/11/19
%Job completed abnormally%	154	138	149	90
%Service%is not started. Current state is stopped%	42	58	135	49

I want i to populate each cell within a row of the Pivot Table with the MAX value for the row. I want it to look like this.

Standard_Event	7/14/19	7/13/19	7/12/19	7/11/19
%Job completed abnormally%	154	154	154	154
%Service%is not started. Current state is stopped%	135	135	135	135

Using the following expression....

=MAX(Aggr(MAX(Aggr(SUM(Case_CNT),Open_Date,Standard_Event)),Standard_Event))

I get the table below. Any ideas as to why the Pivot Table appears the way it does?

Any ideas as to how I get this to appear the way I need it?

Standard_Event	7/14/19	7/13/19	7/12/19	7/11/19
%Job completed abnormally%	-	154	-	-
%Service%is not started. Current state is stopped%	-	-	135	-

Thanks

Re: Using Aggr with a Pivot Table to populate a row of cells with the MAX Row Value

udayreddy06 — Tue, 30 Jul 2019 20:56:24 GMT

Hello,

You have to do it at the script level. Please check if the script in the application make sense.

Regards,
Uday

Re: Using Aggr with a Pivot Table to populate a row of cells with the MAX Row Value

stevenoga1 — Tue, 30 Jul 2019 22:52:01 GMT

Uday...

The Dates and Events represented in the table are changeable dynamically in the app so I do not believe the scripting approach will work. I appreciate the suggestion though.

This issue as presented in my question is a simplification of the true issue I am working. I need to calculate a value based upon the ROW content. Specifically an Upper Control Limit (Mean+2*STDEV). I will be comparing this value against each cell in the Row. Cells which exceed this limit are to be turned Red.

For some reason the AGGR function I have created is unable to apply the ROW Values it produces in a consistent fashion across all the Cells in each Row. Engaging in the simplified example highlighted this as the issue.

I have attached a QVF file that has both the simplified example as well as my real issue. Any additional thoughts on your part would be greatly appreciated.