format number in load line

[#1]

Hello,

I created a dynamic pivot table. I can hide columns and expressions.

My problem now is that i want to format my expressions into specific number like 12.345 or 12.345€.

here is my load line

Expresions:

load * inline [

    FormelName, Formel

    Bestand (Stk), num(num#(sum(Bestand)),#.##0,',','.')                                        <---THIS IS NOT WORKING

    Verkauf (Stk), sum(Menge)

    WE (Stk), sum(Bestand + Menge)

    Abv%, sum(Menge)/sum(Bestand + Menge)

    Bestand EP, sum(Bestand * #EP_STUECK)

    Bestand VK, sum(Bestand * #VK_STUECK)

    Bestand VK Ø, (sum(Bestand * #VK_STUECK))/sum(Bestand)

    Bestand Ausz. VK Ø, sum(Bestand * #VK_VERGL)/sum(Bestand)

    Kalk (B), ((sum(Bestand*#VK_STUECK)/1.2) - sum(Bestand*#EP_STUECK)) / (sum(Bestand * #VK_STUECK)/1.2)

    Umsatz VK, sum(Umsatz_Brutto)

    Einsatz EP, sum(Menge * #EP_STUECK)

    Kalk (V), (sum(Umsatz_Brutto)/1.2 - sum(Menge*#EP_STUECK)) / (sum(Umsatz_Brutto)/1.2)

];

right now all the results have like endless numbers after the comma. Does anyone have an idea how I can format this in a pivot table???

Oh you might need to know that I'm using a macro to build the pivot table!!!

regards

[jjfabian]

Hi there,

try using the "Number" Tab in your pivot table's properties. There you will find several display options for your expressions.

[#3]

I cannot do that because the pivot table is build dynamicly. For example the expression Bestand (Stk) doesnt show all the time. so i have to format it in load line. my pivot table can look on time like this:

          Bestand (Stk)     Verkauf (Stk)

------------------------------------------------------------

701          5                         20

702          1.250                   2000

740          145                      7.456

or it can look like that:

           Verkauf (Stk)

-------------------------------

701          20

702          2000

740          7.456

But at the moment my numbers are still formatet lik 7456,000

[jjfabian]

Sorry, I didn't notioce that before.

In that case, try playing around with your parameters in your num() function. Something like "#." should do the trick

[#5]

Bestand (Stk), num(num#(sum(Bestand)),#.##0,',','.')  this works when i use it directly in the pivot table but not in the script so i tink there has to be another problem

[swuehl]

It's probably just a typo in your post that the num format specifier misses the single quotes?

I think more problematic is that your expressions will probably only read in until the comma / first arg (your INLINE table is comma separated). You'll probably need some kind of quoting (i.e. double quotes around your Formel) here.

[#7]

ok and how can I do that?

[swuehl · Accepted solution]

load * inline [

    FormelName, Formel

     Bestand (Stk), "num(num#(sum(Bestand)),'#.##0',',','.')"

...

I believe you don't need the num#(), so this should work as well

load * inline [

    FormelName, Formel

     Bestand (Stk), "num(sum(Bestand),'#.##0',',','.')"

...

[#9]

Thank you that worked 😉