topic Re: Problem export excel number format num(..) in QlikView

Problem export excel number format num(..)

jonathan_dau — Fri, 11 May 2012 13:28:31 GMT

Hi everyone,

I'm sorry if my issue was already solved in another post but I couldn't find the answer to my problem.

Actually here it is:

I have a table where I'm calculating different stuff as Margins, Volumes of sales and so on.

For one of the indicator I'm calculating volumes for diffents things (volume in terms of turnover or volume in term of number of sales for instance).

As I cannot use one single format for the numbers I use the

- NUM(expression, format-code ('# ##0')) for numerical

- NUM(expression, format-code ('# ##0 €')) for monetary units

Everything's fine so far but the problem is when I export the table on Excel.

Excel doesn't recognize the format I've defined and display the values as Text which is bad when we want to use the Excel file for other calculation.

Do you have any idea of how I can deal with that.

Thanks in advance

Re: Problem export excel number format num(..)

Thu, 06 Sep 2012 19:40:31 GMT

I am having the same issue and have searched the forum for an answer. As you stated, because the expression must be in multiple formats depending on the filter choices, you cannot solve the number export issue by changing the Number Format Settings in the document properties. I had no issues in v9 and have just recently upgraded to v11. I have contacted QV Support and have not received an answer yet. If you received an answer outside of the forum, I would greatly appreciate a reply with the answer. Thanks so much!

Document Expression:

IF(pipecharttype='$',num(sum(if($(vTimeDimension)=$(vTimeDimension3),estimatedvalue))-sum(if ($(vTimeDimension3)=$(vTimeDimension2),estimatedvalue)),'$##,##0;($##,##0)'),num(count(if($(vTimeDimension)=$(vTimeDimension3),opportunityid))-count(if ($(vTimeDimension3)=$(vTimeDimension2),opportunityid)),'#,###0;(#,###0)'))

Re: Problem export excel number format num(..)

Thu, 06 Sep 2012 19:40:42 GMT

Document Expression:

Re: Problem export excel number format num(..)

Thu, 06 Sep 2012 19:40:51 GMT

Document Expression:

Re: Problem export excel number format num(..)

Thu, 06 Sep 2012 19:41:15 GMT

Document Expression:

Re: Problem export excel number format num(..)

m_woolf — Thu, 06 Sep 2012 20:02:43 GMT

After exporting to Excel, you can convert the text to values in Excel via a macro.

Re: Problem export excel number format num(..)

jonathan_dau — Fri, 07 Sep 2012 09:58:36 GMT

I agree with the idea of a macro in Excel but in my case I just chose to use the standard Number Format Settings in the document properties so that every kind of KPI has a numeric format.

I just added in the KPI label a dynamic label giving the correct format depending on the selection.

So when you export in Excel everything's fine and the user can still have a clue of the measuring unit.

However Melanie, if you have any answer from QV support please let me know.

Thank you for your answers.

Re: Problem export excel number format num(..)

Joaquin_Lazaro — Tue, 25 Sep 2012 13:44:35 GMT

Hi:

It was a QV bug, in the next version it will be solved ... read this pdf

Re: Problem export excel number format num(..)

jonathan_dau — Tue, 25 Sep 2012 13:55:05 GMT

Thanks JoaquinIr I'll have a look at this doc and I'll think about upgrading in SR2...

Re: Problem export excel number format num(..)

Joaquin_Lazaro — Tue, 25 Sep 2012 13:59:19 GMT

OK ... but SR2 is closed, not released for the public.

Re: Problem export excel number format num(..)

Tue, 06 Nov 2012 16:52:00 GMT

Hi,

I have the same issue even with v11 SR2 (calculated dimension formatted with num())

did you finally upgrade to SR2 and did it solve your problem ?

Thanks

Re: Problem export excel number format num(..)

Joaquin_Lazaro — Tue, 06 Nov 2012 17:01:38 GMT

Hi richardhooft:

I installed QV 11 SR2 but the bug remains as in SR1.

Look at this message http://community.qlik.com/message/269503#269503 ... I wrote all the test I've been done to check if it was corrected.

Re: Problem export excel number format num(..)

Tue, 06 Nov 2012 17:47:29 GMT

thanks a lot

did you reopen the case with the QlikTech Support ?

Re: Problem export excel number format num(..)

Joaquin_Lazaro — Wed, 07 Nov 2012 07:13:41 GMT

No, I don't know how to do it.

Please, if you do send me the link to have a look.

Re: Problem export excel number format num(..)

Joaquin_Lazaro — Wed, 07 Nov 2012 10:40:12 GMT

Hi:

Following the instructions on this article https://eu1.salesforce.com/articles/Basic/How-to-export-data-to-excel-as-number-in-version-11?popup=true# at the customer portal I've found the way to export numeric fields as numeric cells ... but unformatted.

Re: Problem export excel number format num(..)

amien — Tue, 26 Nov 2013 20:31:12 GMT

anyone still got the document mentioned above from salesforce? link is not working.

TEXT is working, but i really want to have a NUM() to excel.

Re: Problem export excel number format num(..)

sangland2 — Thu, 18 Dec 2014 05:18:03 GMT

Hi, I have 11.2 SR4 and this is still a problem. If a number is formatted as Money on the Number tab then this exports OK. If the expression uses Money and '$#,##0.00;-$#,##0.00' and expression default is used in the number tab, then the QV displayed value is correct, but the value exports to Excel as text. I need conditional formatting of columns and this is done in the expression.

Re: Problem export excel number format num(..)

Tue, 10 Mar 2015 17:16:37 GMT

Hello

I have the same issue in QV 11 SR10 with Num() and export to excel...

Did someone found a workaround ?

Thanks

Re: Problem export excel number format num(..)

Joaquin_Lazaro — Wed, 11 Mar 2015 07:45:22 GMT

Hi claudiasoares

There are two possibilities send numbers formatted as text (with dots and periods) but not excel numbers or send real numbers in excel but without numeric format.

Follow this instructions and chose which one you like it.

Hope I helped you

Joaquín

Re: Problem export excel number format num(..)

sangland2 — Wed, 11 Mar 2015 08:45:41 GMT

Thanks very much. That option works OK.