THE MEASURE KEPT AT 'AUTO' NUMBER FORMATTING IN TABLE CHANGES TO TEXT WHEN EXPORTED IN EXCEL

[diwakar_nahata]

Requirement is as below:

Toggle button :X and Y

Metric :On selecting X -num(Sum(Sales),'$#,##0;-$#,##0')

               On selecting Y -num(Sum(Quantity),'#,##0')

We are getting values for a measure in Dollars($).The Number formatting for this measure is kept to 'Auto'.However ,when the object is exported to excel the values are exported as Text (refer attached screenshot), hence making it difficult for user to perform further calculations 

What have you tested to solve the issue?

1.Tried changing the Number formatting to Measure Expression ,but the issue still persists.

2.We cannot use the Number formatting option as Money as in a single measures we have 2 expression which get toggled by changing the button mentioned above If Number format is set to 'Money',it gets applied to the entire expression which does not fulfill our requirement

[Sue_Macaluso]

@diwakar_nahata Are you using QlikView or Sense? I would like to move this into the correct product forum

[diwakar_nahata]

I am using Qlik Sense

[Noor51]

I have the same problem. I would love to know how to solve this issue. 

[Chanty4u · Accepted solution]

Try this 

if(GetSelectedCount(ToggleField) > 0,

    if(GetSelectedCount(ToggleField, 'X') > 0,

        num(Sum(Sales), '$#,##0;-$#,##0'),

        num(Sum(Quantity), '#,##0')

    ),

    num(Sum(Sales), '$#,##0;-$#,##0') // Default formatting when toggle selection is not made

) as FormattedValue

[cotiso_hanganu]

I have a similar, but slightly different situation, where the formula controls the number formatting, depending on the value in one of the dimensions, toggling between money, percent and numeric formats.
(All three formats are simultaneous required in the same column if the same object, within the same selectin context , on different rows...)
The export with Auto formatting is providing only text values, while the number formatting is forcing  out percent and money.

Any suggestions !?