Chart Excel export is all text and an alternative to Valuelist()

[imac]

Hi everyone

I have a requirement to create a YTD vs Previous Year month by month over year comparison for Budget, Actual, and Actual/Budget for Margin and Volume of sales for both results vs actual. I have added a pivot chart, and the dimension I am using Month and two calculated dimensions

Month

=valuelist('Margin','Volume')

=valuelist('Results','Actuals vs.')

My expressions are as below

Actuals:

if(

valuelist('Margin','Volume') = 'Volume',

if (valuelist('Results','Actuals vs.') = 'Results',

num(sum({$<Year = {'2018'}>}Volume)/1000,'#,##0.0'), ''),

if(valuelist('Margin','Volume') = 'Margin',

if (valuelist('Results','Actuals vs.') = 'Results',

num(sum({$<Year = {'2018'}>}[Margin])/1000,'#,##0'),''))))

And so on for Budget, a bunch of if/else statements... and as you can see, Volume needs to be in one decimal, while Margin is a whole number without decimal. Because I have to format the result within the expression itself, I left the "Numbers" setting of the chart to Expression Default

The results I get in the chart give me what I need, but when the data is exported to Excel, everything is sent as text.

I am SURE there must be a better way of getting the same chart I need without resorting to the valuelist dimension the way I am doing it now, but since I am trying to learn Qlikview by myself without formal training, this is what I came up with

If anyone can provide any suggestions (on both issues - exporting as text, as well as if there is a better way of doing the same thing) I would really appreciate it.

[imac]

Im really struggling with this, any suggestions truly appreciated!

[lfholland]

I'm a novice, so I may not be much help, but the only thing I can think of is you can right click on the table and choose export instead of send to excel.  You can then choose to export the data in a .csv file which will still open in Excel.  This should strip the formatting and send it as numbers instead of text.

[lfholland]

Another option (if you can't find a solution) is to convert it to number in excel.  All you have to do is highlight the column, click on text to columns in the data tab.  Choose "Delimited" and click next. Uncheck any of the delimiters and click next.  Click finish.  Hope this helps!

[imac]

Thanks Laura, that's what the users have been instructed to do, but its annoying and they have asked if there is a way to stop doing this so "it just works"...

I am hoping the folks here will have suggestions, but does not look like there may be a solution. Still hopeful though.