Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Row Total in a pivot table
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Row Total in a pivot table
 Login/Join
 
Member
posted
I have a pivot table with date and minutes, I'm not able to get a row total for the minutes. Such as:
2018/2/3 - 70 min
2018/2/9 - 36 min
2018/2/10 - 20 min
SUM weekly_mins ROW-TOTAL
This gives me a zero.
Any ideas on how to solve this?
Thanks people.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8105
Windows, Excel
 
Posts: 15 | Registered: November 03, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
The ROW-TOTAL key word is normally for an ACROSS total, not a column total...

What is the format of your minutes field ? Is it a text field ?
If so, you cannot have a total on a text field.

Please share your code.


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2192 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
You're correct, the minutes is stored as text.

SUM
WEEKLY_MINS
BY LOWEST
EMPLID
ACROSS LOWEST 'End Week'

This is a row total not column.

This is something of an output:

2018/01/14 2018/01/21 2018/01/28 2018/02/04 2018/02/11
93 96 111 124
79 113 124 225 134
Thanks


WebFOCUS 8105
Windows, Excel
 
Posts: 15 | Registered: November 03, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:

You're correct, the minutes is stored as text.


So I repeat myself :
quote:

you cannot have a total on a text field

Only numeric values can be summarized.
So you will have to create your row-total based on a numeric value of WEEKLY_MINS

Do not think that because you are using SUM as the verb that mean WEEKLY_MINS has been summarized in your below code
TABLE FILE ...
SUM WEEKLY_MINS
BY LOWEST EMPLID 
ACROSS LOWEST 'End Week'
...
END

it only specified to focus to "Group" the information based on the BY/ACROSS field.

If WEEKLY_MINS was a numeric field, yes SUM will have it summarized but in this case since it seems that only one value exist per EMPLID / EOW_DT it shows the proper value otherwise it will have shown the last one.
See below sample
-* This one gives you all existing car value per Country / Seats
TABLE FILE CAR
PRINT CAR
BY COUNTRY
ACROSS SEATS
END

-* This one gives you only the last car value per Country / Seats
TABLE FILE CAR
SUM CAR
BY COUNTRY
ACROSS SEATS
END

In conclusion : you need numeric value to have your row-total


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2192 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Row Total in a pivot table

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.