Planned
Managing null value symbol in Pivot
As it exists in the Table object, could it be possible to have the option to define the null symbol in the Pivot?
As it exists in the Table object, could it be possible to have the option to define the null symbol in the Pivot?
We do have a null value styling option (under Appearance) however, we do not currently offer the possibility to define the null symbol in the Vizlib Pivot Table (as it is the case in the Vizlib Table). This is a good one to ask for the Vizlib Pivot Table! Thanks for your feedback!
In fact this is something a bit annoying when exporting the data in Excel.
If you use Sum(<range>), the char '-' is not causing issues. But, if you are doing <cell1>+<cell2>, in this case, it generates an error.
I don't want to put the pressure, just to illustrate the context, we are planning to move hundreds of users from QV to QS and this is a showstopper (Excel is evil ;-))
1 person likes this
Our controllers that use the Excel extracts are naging on me because of the additional workload to remove the dashes. So they would be thankful if I could display a blank or a zero instead.
Thanks
(Sorry if this double-posts, but I didn't see it post the first time.)
Apologies if you tried it already, but the NullInterpret variable does wonders when working with Excel/text files: https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/ValueHandlingVariables/NullInterpret.htm
It allows you to universally map a character to become null upon read -- meaning you don't have to change logic field-by-field, just set the variable in your Main ETL script for each app and let it do its magic!
The NullInterpret settings is handling the way NULL values are interpreted during data loading, if I'm not wrong.
Not the way they are interpreted in the screen or during the export of a table.
Qlik's native null handling won't impact the way nulls are displayed within an object, unless you actually change the value during load (using NullDisplay, NullInterpret, or NullAsValue / NullValue). You can replace null values within your dimensions, but if a pivot would have a null value in a measure, you can't impact that using native functionality.
In the attached screenshot, you can see an example where I replaced the null values with the text "Null", but the end result is still nulls created during the pivoting action when no measure values exist for a specific combination of dimensions. Obviously, this replacement also has the negative aspect of being treated as a value, rather than as null (so suppress null / zero will no longer work).
Sorry I misunderstood the issue.
Although built-in object function is always nice why not wrapper whatever you want to display with Null logic -- e.g. Sum(Value) becomes if(IsNull(Value), '$(vNullDisplayString)', Sum(Value)). Not as elegant, but solves the problem.
I see 2 drawbacks to your method:
-What is the impact on the performances with complicated aggregation functions done twice (ok qlik engine is maybe smart enough).
- Moreover, it doesn't cover 100% of the cases, in a pivot, you can have intersections where there is no matching data at all in our datamodel. For example, you sell product A only in 2018, and product B only in 2019, you will not be able to perform the test for cell B/2018 and A/2019 are Sense is not evaluating the expression.
You don't want side-effects don't take the drug and keep the malady ;)
Re: above
1 - design / performance tradeoff decisions as always :)
2 - if you have chosen to display nulls in your pivot table I think the proposed works but would admittedly need to mock up. regardless: you can force the issue by adding purposeful null value fact records to all dimension intersections in your pivot table space as part of the ETL intake for the app (again, comes with its own "drawbacks" -- but you have to decide what is important to your users and make tough choices)
Nice thing about Qlik is you HAVE these options which you can choose not to use.