topic Re: format number in load line in QlikView

format number in load line

Mon, 25 Jun 2012 10:27:32 GMT

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

Re: format number in load line

jjfabian — Mon, 25 Jun 2012 10:37:13 GMT

Hi there,

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

Re: format number in load line

Mon, 25 Jun 2012 10:42:19 GMT

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

Re: format number in load line

jjfabian — Mon, 25 Jun 2012 10:59:12 GMT

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

Re: format number in load line

Mon, 25 Jun 2012 11:15:40 GMT

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

Re: format number in load line

swuehl — Mon, 25 Jun 2012 11:21:55 GMT

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.

Re: format number in load line

Mon, 25 Jun 2012 11:27:57 GMT

ok and how can I do that?

Re: format number in load line

swuehl — Mon, 25 Jun 2012 11:31:04 GMT

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',',','.')"

...

Re: format number in load line

Mon, 25 Jun 2012 11:55:27 GMT

Thank you that worked 😉