Picking unassociated values straight table

[Anonymous]

Dear All

I have a report where in there are customer names and transactions shown for each customers.

As per the associative principle the customers who have not done any transactions are not displayed in the report.

However I have a requirement to show even those customers and display '0' in the transactions column for such customers. 

Is there any way to achieve this result?

Thanks & Regards

Chintan Gala

[asgardd2]

Hello!

For example, I created data model:

Clients:
LOAD * INLINE [
Client_ID,Clien_Name
1,HP
2,Acer
3,Samsung
4,HTC
5,Apple
];


Transactions:
LOAD * INLINE [
Client_ID,DOC_NAME,DOC_DATE,AMOUNT
1,01-1/2016,01.02.2016,5
1,01-2/2016,02.02.2016,1
1,01-3/2016,10.02.2016,5
1,01-4/2016,11.03.2016,10
1,01-5/2016,11.04.2016,9
2,02-1/2016,02.03.2016,7
2,02-2/2016,02.04.2016,2
2,02-3/2016,02.05.2016,7
4,03-1/2016,03.02.2016,6
4,03-2/2016,13.02.2016,1
];

To fix it  not choose checkbox "Suppress Zero-Values" in settings :

See attachment file.

[Anonymous]

Thanks Anton for your reply. The above solution works but only without selections of month and year.

As soon as I select a particular month and year, the transactions which hold 0 values disappear.  

I want them to be displayed even on selection of month and year.

Regards

Chintan Gala

[jonathandienst]

You need to have the customer names and months in separate tables, like this:

LOAD * Inline

[

  Cust, Date, Value

  1, 2016/12/1, 100

  1, 2016/11/1, 75

  1, 2016/9/1, 70

  2, 2016/12/1, 82

  2, 2016/10/1, 44

  3, 2016/11/1, 55

  3, 2016/10/1, 65

  3, 2016/9/1, 45

];

LOAD * Inline

[

  Cust, Name

  1, One

  2, Two

  3, Three

];

LOAD

  Date,

  Month(Date) as Month

Inline

[

  Date

  2016/12/1

  2016/11/1

  2016/10/1

  2016/9/1

];

Create a table with "Name" (and optionally "Month") as dimensions and Sum(Value) as the expression. For the Name dimension, check the "Show All Values" and "Suppress When Value is Null" options. Make sure Name is the top of the Sort list and use this expression to sort by Name =Only({1} Cust).

No selections:

Month selected:

[Anonymous]

Dear Jonathan

Many thanks for your solution. it works as needed , only thing missing is for those Transactions where there is 0 the month is not displayed in the report. Is it possible to display the month instead of '- '.

Thanks & Regards

Chintan Gala

[jonathandienst]

I am not sure is that is possible without altering the data or the sense of the table.

You cannot use a calculated dimension to handle the problem. The '-' here is a "missing", not a null, and the calculation will be ignored for a missing value.

You might need to create dummy zero values, or move the Month to an expression rather than a dimension.

This may be useful: NULL handling in QlikView

[Anonymous]

Thanks Jonathan!  I will try the options suggested by you..