topic Export issue in pivot table with different number format in the same expression in QlikView

Export issue in pivot table with different number format in the same expression

navitesh — Fri, 01 Jun 2018 14:37:17 GMT

Hi everyone,

I am working on a requirement, in which we are showing a Pivot table in which the expression has multiple number format based on the dimension field value. We are able to achieve the different number format, however, when we export the table into excel, the value of the numbers are getting exported as text, which as I understand is expected when you select 'Expression Default' format in the number tab of the chart property.

Please find the attached workbook with a mock version of what we are trying to achieve.

Below are some screenshots

Expression definition:

Pivot Table in QlikView

Excel Export :

Instead of the value getting exported as text in excel, we want to get them as numbers in the defined format.

Note: One thing we found out is that, if the entire expression uses the same number format in the 'num()' function even if there are multiple if else condition with different formulae, the export still retains the values as numbers but the format resets to default.

Thanks in advance,

Navitesh

Re: Export issue in pivot table with different number format in the same expression

el_aprendiz111 — Fri, 01 Jun 2018 17:19:37 GMT

Hi,

Re: Export issue in pivot table with different number format in the same expression

navitesh — Sat, 02 Jun 2018 15:28:33 GMT

Hi Jairo,

Thanks for the reply, however this is not the solution i am looking for. Doing this will keep the format of the entire column and row as fixed with two decimal. What i am looking for is to have two different formats in the same expression for two different values of dimensions amd to export the same as numbers in excel. Please have a look at the attached qvw file, it will clear the requirements.

Navitesh

Re: Export issue in pivot table with different number format in the same expression

azimabadi — Wed, 06 Jun 2018 12:22:15 GMT

Hello everybody,

i have the same problem with QV 12.

Anybody who KNOWS what is QlikView has any idea? (please don't post BS, just read the problem and answer if you really understand it and have an answer, many thanks!)

Regards

Peyman

Re: Export issue in pivot table with different number format in the same expression

Florentin74 — Tue, 31 Jul 2018 12:19:52 GMT

Hi,

I have the same issue 😞 ..

No solution as far as I can see.

"Send to excel" and it fails.. But a manual right click copy full table and then inserting it manually in excel works…

Regards,

Anders

Re: Export issue in pivot table with different number format in the same expression

navitesh — Tue, 31 Jul 2018 12:25:45 GMT

Hi Anders,

We also came to the same conclusion, there is no way to retain the format using the "send to excel" option. For not we decided to educate the user about copying the data manually and the pasting it in excel.

Ragards,

Navitesh

Re: Export issue in pivot table with different number format in the same expression

neelamsaroha157 — Tue, 31 Jul 2018 13:16:13 GMT

Setting the value of ExcelExportMixedAsText to 0 is able to solve the issue for 1st three columns but the % column is also expressed as number.

If this works for you then you can find ExcelExportMixedAsText variable in help menu-> About Qlikview-> right click on qlik symbol.