What is the format to use to display large numbers ?

[Martin Yergeau]

I remember that a new format (or a function) has been introduced to display large numbers with the "M" & "K" annotation for million and thousands but I can't find it.

Searching in product documentation gives hundred of pages of document that are, for most of then, unrelevant to my serach.

Does someone can help ?

Regards,

[Debra Waybright]

Try COMPACTFORMAT -- I have a vague note about this, I guess it is a function? My note says, "determines highest value of data and displays abbreviated alpha based on data -> $3K"

[David Beagan]

With the newer field formats, fields can be displayed in millions or billions and many other formats. This example from the Describing Data With WebFOCUS® Language, Page 127 shows how:

DEFINE FILE GGSALESNEWDOLL/D12.2 = DOLLARS * 100;ENDTABLE FILE GGSALESSUM DOLLARSNEWDOLL/D12.2nM AS 'Millions'NEWDOLL/D12.2nG AS 'Billions'BY CATEGORYON TABLE SET PAGE NOLEADON TABLE SET STYLE *GRID=OFF,$ENDSTYLEEND

[Martin Yergeau]

Thanks David,

That what I was looking for but it is not available in the version I must use.

[Debra Waybright]

What version are you using? If the simple format isn't available, then the function probably isn't either. But here's the syntax in case someone else is looking for it. This is what is in the Functions Users' Manual:

TABLE FILE WF_RETAIL_LITE

SUM DAYSDELAYED

QUANTITY_SOLD

COGS_US

COMPUTE CDAYS/A30= COMPACTFORMAT(DAYSDELAYED);

CQUANT/A30= COMPACTFORMAT(QUANTITY_SOLD);

CCOGS/A30= COMPACTFORMAT(COGS_US);

ON TABLE SET PAGE NOPAGE

ON TABLE SET STYLE *

GRID=OFF,$

ENDSTYLE

END 

[Martin Yergeau]

Hi Debra,

The only thing that does COMPACTFORMAT seems to remove separators and dollars sign, not adding the thousand (K) anotation



TABLE FILE WF_RETAIL_LITE SUM DAYSDELAYED COMPUTE CDAYS /A30 = COMPACTFORMAT(DAYSDELAYED); QUANTITY_SOLD COMPUTE CQUANT /A30 = COMPACTFORMAT(QUANTITY_SOLD); COGS_US COMPUTE CCOGS /A30 = COMPACTFORMAT(COGS_US); ON TABLE SET PAGE NOPAGE ON TABLE SET STYLE * GRID=OFF,$ ENDSTYLE END

Not working 😥

[David Beagan]

I tried the COMPACTFORMAT code in version 9 and it does include the letter:

Looks like it was only parially implemented in your release. I wonder if COMPACTFOCMAT was even documented in the functions manua your release?

[Debra Waybright]

Interesting. It must have to do with the version you are running? It will give me M for millions, but not K for thousands.

[Martin Yergeau]

Work in 8207+ but not prior to this and I have a 8204 environment where I am trying to "compact" the numbers.

So seems that I won't have other choices than coding it

[Debra Waybright]

Ah. It is frustrating sometimes when you have to work in an older version. Maybe you can write your own function! :)

[Debra Waybright]

We have some in-house written WebFocus functions. Some are used to run in-house written SQL functions, some to do WebFocus commands. The basic syntax for the WF function:

DEFIINE FUNCTION FUNCTION_NAME (INPUT_PARM/format)

<code>

FUNCTION_NAME/format = <return value>;

END

We put each function in a fex file with the function name (FUNCTION_NAME.fex) and save it in the baseapp folder. I think it can be in any folder in the application path.

Then you have to define the function to the system. This means updating the EDASPROF.prf file with an include:

-INCLUDE FUNCTION_NAME

I hope this helps!

[Martin Yergeau]

Thanks Debra,

Already have the doc from "Creating Reports With TIBCO® WebFOCUS Language"

My challenge now it that, since I am using a custom function "short number" which is returning an alpha field, I now have to manually creates the total report rows and also have them converted in a "short number" annotation, merge with detail and display properly.

I know how to do it (did several times), but just a pain since the "nK", "nM" and "nG" field format are still numeric fields that can be summarized.

Wish I had a newest version to use these new functionalities...

[David Beagan]

@Debra Waybright

I found a nice feature that could let you avoid having the define function code run every time in the edasprof.prf. Suppose in a function_lib.fex you had (as well as other functions):

DEFINE FUNCTION CUBE(n/P9) CUBE/P9 = n * n * n; END

then in your report fex you could code:

TABLE FILE ggsalesPRINT SEQ_NOCOMPUTE CUBED/P9 = DF.FUNCTION_LIB.CUBE(SEQ_NO);WHERE SEQ_NO LE 20END

This is documented in Describing Data manuals as far back as 8.2.01 for use in Defines in a master file. But it does not appear in the Creating Reports manuals, even though it works in reports. So I guess it would be debatable about whether this was officially supported for use in reports.