topic Re: How to show both zero rows and non zero rows in App Development

How to show both zero rows and non zero rows

Anonymous — Sat, 21 Jan 2017 00:01:51 GMT

Hi all,

I have an issue and I wanted to get your inputs.

First some context...

One of the advantages in QLIK is the "full outer join" default. This means we always have all of the data. Except it doesn't....

When we filter on a filter value, the dependent table in a sheet removes all values that don't meet that criteria. How do we keep the values that don't meet that criteria. Allow me to explain.

I have a table in a sheet. It sums costs and total counts of orders. When I leave the filters blank I get the complete cardinality of my customers. When I select a month, say "Jan 2017", The table filters for all my customer who have placed an order in Jan. 2017. But this is not what I need in this instance. I need the table to sum the values for Jan, 2017 for all customers that placed an order in Jan, 2017. and display "0" for any customers that have NOT placed an order in Jan. 2017. I do not want the table to filter the set of customer, I want it to continue to show all customers. This way I can see who placed orders and how much, AND I can look at my customers who have stopped placing orders and perhaps check in them and see if they need anything.

Thanks in advance for your input...

With no filters---

After I select a month...

Re: How to show both zero rows and non zero rows

rubenmarin — Sat, 21 Jan 2017 00:26:39 GMT

Hi Christopher, that's the default behaviour, it helps removing data you are not setting the focus, but it can be avoided using set analysis to ignore selections, try adding +Sum({1} 0) to your expression:

Sum(Costs)+Sum({1} 0)

Re: How to show both zero rows and non zero rows

petter — Sat, 21 Jan 2017 01:50:50 GMT

By adding a null value for each OrgNodeID for each month you can always see all OrgNodeIDs in each month:

😧
LOAD 
 *
INLINE [
OrgNodeID, OrderMnth, Cost
A, 1 , 1
A, 3 , 2
A, 4 , 3
B, 2, 4
B, 3, 5
B, 5 , 6
];

nBiz = FieldValueCount('OrgNodeID');

// This will add rows to the above table since the fields are the same (called auto concatenation)
LOAD
    FieldValue('OrgNodeID',RecNo()) AS OrgNodeID,
    IterNo() AS OrderMnth,
    Null() AS Cost   // By putting in NULL the Count(TOTAL Cost) will be correct too
AUTOGENERATE
  $(nBiz)
WHILE IterNo()<=12 ;  

nBiz=;

Re: How to show both zero rows and non zero rows

Anonymous — Mon, 23 Jan 2017 18:24:42 GMT

Thank you both for your answers. In this instance I want to avoid creating extra data in the tables since this dashboard will not be limited to looking at "who did/did not placed orders this month" and may even go down to "who did/did not placed order on this day". Also, the end users for this app will not have the ability to alter the data load, and I can anticipate all the scenario's that they will request. Thank again to you both.