Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jblomqvist
Contributor II

How to replace hyphen or ' - ' with 'No Data' in a Pivot Table?

Hi all,

I have the following example:

How do I replace the Dashes with 'No Data' instead?

I tried this as a Measure but it does not work: If(Num(Sum(LineSalesAmount), '$##,###.00')=Null(), 'No Data', Num(Sum(LineSalesAmount), '$##,###.00'))

6 Replies
miguelbraga
Valued Contributor III

Re: How to replace hyphen or ' - ' with 'No Data' in a Pivot Table?

Try this please:

=If(IsNull(Num(Sum(LineSalesAmount))), 'No Data', Num(Sum(LineSalesAmount), '$##,###.00'))


If you make some change in your script it will make you easier to understand the expression like this:

[YourTable]:

LOAD     *,

            Num(Sum(LineSalesAmount)) as TotalNumSales

FROM [yourconnection];

By using TotalNumSales you can make the expression in pivot table like this:

=If(IsNull(TotalNumSales), 'No Data', TotalNumSales)

Hope this helps you

Regards,

MB

Highlighted
tkendrick20
Valued Contributor

Re: How to replace hyphen or ' - ' with 'No Data' in a Pivot Table?

If(IsNull(Sum(LineSalesAmount)),'No Data',Num(Sum(LineSalesAmount), '$##,###.00'))

yadhunandhanan
New Contributor II

Re: How to replace hyphen or ' - ' with 'No Data' in a Pivot Table?

Hi John,

In Pivot table Presentation tab have two field "Null Symbol" and "Missing Symbol", just change there what you what to show in null field or missing field.

Not applicable

Re: How to replace hyphen or ' - ' with 'No Data' in a Pivot Table?

Hi Yadhunandhanan,

Your solution works for Qlik Sense , If yes, where that option comes ?

yadhunandhanan
New Contributor II

Re: How to replace hyphen or ' - ' with 'No Data' in a Pivot Table?

Hi Rohit,

Sorry Rohit. My solution is not for Qlik Sense. Its for QlikView. 

jumuravchik
New Contributor II

Re: How to replace hyphen or ' - ' with 'No Data' in a Pivot Table?

Does anybody know what the solution is for Qlik Sense?