Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

navitesh
New Contributor II

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

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:

Experssion Definition.png

Pivot Table in QlikView

Pivot Table.png

Excel Export :

Excel Export.png

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

Tags (2)
6 Replies
el_aprendiz111
Valued Contributor

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

Hi,

fmtJn.png

navitesh
New Contributor II

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

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

azimabadi
Contributor III

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

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

Partner
Partner

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

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

navitesh
New Contributor II

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

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

neelamsaroha157
Valued Contributor II

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

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.

Capture12.PNG


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