Individual Filter selections should not filter out rows but zero out/nullify respective measure values in the table chart columns

[Qlikerrr]

_/\_ Kindly help with the below _/\_

Requirement 1:

In the dashboard, on applying the filter "ETA Date Range" (Date Picker) on the sheet, the table chart should not filter out or hide any rows. rather, the values of one measure (Open PO column) of those rows, which otherwise would have gotten filtered out in the table should become NULL or ZERO. And all those other columns which are dependent in the Open PO calculations (Shortage Qty, Shortage Value, MTD Value, MTD Quantity, MTD vs LP) should show values according to the updated calculated values.

Requirement 2:

Similarly, on applying the filter "Storage Location" on the sheet, the table should not filter out or hide any rows. rather, the values of two measures (Stock Available and In Transit columns) of those rows, which otherwise would have filtered out in the table should become NULL or ZERO. And all those other columns which are dependent in the Stock Available and In Transit calculations (Shortage Qty, Shortage Value, MTD Value, MTD Quantity, MTD vs LP) should show values according to the updated calculated values.

Attached the .qvf file with data, for reference

Data Model:
Data Model

Sheet:

Table Chart:

Dimensions: 

• material_id
• Description

Measures: 

• Net Sales Qty; The Expression is Sum([Net Sales Volume])
• Net Sales Value; The Expression is Sum([Net Sales Value])
• Net Sales Currency ; The Expression is currency
• Open Order Qty; Expression is Sum(oo_qty)
• Open Order Value ; The Expression is Sum(oo_value)
• OO Currency; The expression is currency
• Billing Pend Qty; the expression is Sum([Pend Invoice Qty])
• Billing Pending Value ; The Expression is Sum([Pend Invoice Value])
• Billing Pending Currency; The Expression is currency
• Stock Available ; The Expression is Sum(Stock)
• In Transit; The Expression is Sum(in_transit_qty)
• Open PO; The Expression is Sum([PO Open Qty])
• ETA; The Expression is =date(delivery_date)
• Shortage Qty; The Expression is

if(SUM(AGGR(COALESCE(COALESCE(([Stock Available]),0)-COALESCE(([Open Order Qty]),0)+COALESCE(([Billing Pend Qty]),0)+COALESCE(([Open PO]),0),0),MatDes))>=0,

0,

SUM(AGGR(COALESCE(COALESCE(([Stock Available]),0)-COALESCE(([Open Order Qty]),0)+COALESCE(([Billing Pend Qty]),0)+COALESCE(([Open PO]),0),0),MatDes)))

• Shortage Value; The Expression is [Shortage Qty]*[Open Order Price]
• MTD Qty; The Expression is (coalesce([Open Order Qty],0) + coalesce([Shortage Qty],0)+coalesce([Net Sales Qty],0))
• MTD Value; The Expression is (coalesce([Open Order Value],0) + coalesce([Shortage Value],0)+coalesce([Net Sales Value],0))
• LP Qty; The Expression is Sum([LP Volume])
• LP Value; The Expression is Sum([LP Value])
• LP  Currency; The Expression is currency
• Sales vs LP ; The Expression is =[Net Sales Value]+[Open Order Value]-[LP Value]
• MTD vs LP; The Expression is  (coalesce([Open Order Value],0) + coalesce([Net Sales Value],0)+ coalesce([Shortage Value],0))-[LP Value]

Filter Fields:

• ETA Date Range (Date Picker) ; The Expression is =date(delivery_date)
   

[Qlikerrr]

If anyone feels the question is confusing/ not clear enough, kindly comment and I will try to articulate in a simpler way.

Thanks in advance for the kind gesture.

[marcus_sommer]

I assume your challenge is not to ignore certain selections - which could be simply solved by adding the relevant fields in a set analysis - else to access NULL.

NULL isn't stored in any way and couldn't be accessed. NULL within the table-loads could be queried and replaced with real values, like: '<NULL>' with functions like: coalesce(), len(trim()), isnull() with/without if-loops or the use of NULL variables / NULL mapping. After that the replacement-values could be directly addressed to be included/exclude like needed.

But the above won't be working if the NULL is the result of missing key-values of a table-association. In this case the solution is similar by populating the missing key-values - running each table against each other to find the missing ones and to add them appropriate.

Simpler as this is usually to skip this association-stuff and to develop the data-model as the recommended star-scheme with a single fact-table and n dimension-tables. 

[Qlikerrr]

@marcus_sommer 
Thank you so much for taking time to respond.

let's ignore the NULLs part for now.
Please guide on different approaches as to how to show all rows in the table chart where the selected values show the values as expected but to fill zeros wherever the filter criteria don't apply (for both the requirements as discussed in the question).

Be it SET analysis OR conditional expressions OR using alternate states etc.

Kindly share the measure expressions to be used.

Thanks _/\_

[marcus_sommer]

I'm not sure if the NULL matter could be simply ignored ... You may check your view by enabling the chart-property of displaying ZERO and/or to wrap your expression, like: rangesum(YourExpression, sum({1} 0)).

If this isn't working as expected you will have definitely have a NULL issue which is very likely caused from the data-set and/or the data-model and which should be resolved on this level. I don't want to say that there no ways to show NULL and/or missing values only with UI measurements but the complexity of such workarounds will be significantly higher as a script-solution by getting serious disadvantages in regard to the performance and the usability.