Showing NA instead of hyphen in pivot table in Qliksense

[jatin222kolhe]

Hi,

We have created a pivot table which has country and yearmonth as dimensions where yearmonth will be columns.

Our expression is if( len(concat(KPI))>0, count(KPI),'NA').

The result we get is it table shows NA if KPI values doesnt exists for any Yearmonth and country but for one

country , it is not showing NA but showing Hyphen  for few months.

We are not sure what this issue can be.

Same scenario for another pivot table but in the other table it not showing NA at all even after using similar expression as above.

[SunilChauhan]

try to replace Expression with below

 if( len(concat(KPI))>0 or  isnull(KPI)=0  or Trim(KPI) <> '', count(KPI),'NA').

or explore below link for implementing at app level 

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegu... 

[TauseefKhan]

Hi @jatin222kolhe,
To display 'NA' instead of a hyphen, you can modify your expression to handle null values explicitly. 

Check this both will work:

=If(Len(Concat(DISTINCT KPI)) > 0, Count(DISTINCT KPI), 'NA')

OR
=If(Len(Concat(KPI)) > 0, Count(KPI), If(IsNull(Count(KPI)), 'NA', Count(KPI)))


** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. **

[Firefly_cam]

This happens because there is no association between Country and YearMonth in Data Model. And no formula can cover this absence.
There are 2 options here:
1) cover the associations with missing data in Data Model (if there is no combination of Country-YearMonth, need to create it)
2) go through synthetic dimension which is pretty heavy and consuming:
Put as your Country dimension in Pivot:

=Valuelist($(=Concat(Distinct CHR(39)&Country&CHR(39),','))) 

Then your measure will be

Pick(Match(Country,Valuelist($(=Concat(Distinct CHR(39)&Country&CHR(39),','))))+1,'NA',count(KPI))

[MatheusC · Accepted solution]

Hi @jatin222kolhe 

You are certainly getting null values ​​due to the crossing of information contained in the table, with Country, Month/Year and Its Measurement, as @Firefly_cam  already mentioned.

Qlik has made a new pivot table available in the February 2024 version, which makes this treatment simple. If you are on this version, it can be found under Custom Objects.

https://help.qlik.com/pt-BR/sense/February2024/Subsystems/Hub/Content/Sense_Hub/Visualizations/Visua...

There is also the Vizlib extension, if you have the possibility to work with extensions, this is also an alternative.

Regarts, Matheus

[Firefly_cam]

Thanks for mentioning this new feature. I was certain that I’ve seen this, but couldn’t find in which release)

[jatin222kolhe]

Hi,

Since in the data model, we dont have those yearmonths for those countries, we are not able to show  NA even after trying multiple ways.

So it seems there are 3 ways:

1)we can populate the missing year months for those countries in the data model along with the primary key which will increase the no of rows(in millions) and can affect the applications performance.

Also it will show wrong values in charts if we are counting the primary key itself.

2)using vizlib pivot table which has option to replace hyphen with NA or

3)using the pivot table in latest qlik release which you mentioned which has option to replace hyphen with NA