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

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

jblomqvist — Tue, 16 Aug 2016 15:40:33 GMT

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'))

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

miguelbraga — Tue, 16 Aug 2016 16:36:50 GMT

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,

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

TKendrick20 — Tue, 16 Aug 2016 17:14:22 GMT

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

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

yadhunandhanan — Wed, 17 Aug 2016 10:10:49 GMT

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.

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

Wed, 17 Aug 2016 11:05:11 GMT

Hi Yadhunandhanan,

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

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

yadhunandhanan — Wed, 17 Aug 2016 12:01:42 GMT

Hi Rohit,

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

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

jumuravchik — Thu, 18 Oct 2018 15:08:18 GMT

Does anybody know what the solution is for Qlik Sense?

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

eng19872 — Thu, 23 May 2019 13:36:37 GMT

Is there any solution ? I am also looking for the same solution to replace hyphen with 0 or 'No Data'.

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

Madhab77 — Tue, 03 Sep 2019 11:17:14 GMT

Hi,

Did you get any solution for this? I also facing the same issue, and need the solution.

Thanks

Madhab

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

eng19872 — Thu, 05 Sep 2019 13:22:39 GMT

Yes, I resolved. Here is the solution

If(write your expression, true, ' ')

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

Madhab77 — Mon, 09 Sep 2019 08:58:17 GMT

Hi Eng19872,

Thanks for the update, but unfortunately, it does not work. My Pivote table is a bit complicated one! If you analyse my screenshot of the table, you will find that, for a particular date there is no record, but in the next date records are there, so qlik is writing that records, which are available, but where there is no records, qlik simply putting a dash('-'). I think it is qlik's internal setting, like excel the default it keeps blank.

Thanks, Madhab.

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

simon_minifie — Mon, 09 Sep 2019 09:18:01 GMT

Hi,

Have you tried using the ALT function?

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunctions/alt.htm

Thanks,

Simon

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

Madhab77 — Mon, 09 Sep 2019 09:33:46 GMT

Hi @simon_minifie , thanks for the update,. Yes, I tried with that also, but no luck.