Show customers with 0 sales

[wanyunyang]

Hi guys,

My data has daily sales by customer. However, if I selected a date, customers with no sales on that day would not show up. Is it possible to show customers with 0 sales as well?

I created a simple example for this:

load * Inline
[
Customer, Sales, Date
A, 200, 01/01/2019
B, 300, 01/01/2019
A, 400, 01/02/2019
C, 700, 01/03/2019
];

If I selected 01/01/2019, only A & B would show up:

How can I let C show as well, with 0?

Thanks for help!

[skamath1 · Accepted solution]

Replace your expression with 

RangeSum(Sum(Sales), Sum( {<Date= >} 0))

[bhargav_bhat]

Hi Wanyunyang,

Add this is script and it will work

Sales:

load * Inline
[
Customer, Sales, Date
A, 200, 01/01/2019
B, 300, 01/01/2019
A, 400, 01/02/2019
C, 700, 01/03/2019
];

noconcatenate

SalesModified:

Load 
distinct  Customer 

Resident Sales;

join(SalesModified)

Load

distinct Date

Resident Sales;

left join(SalesModified)

LOAD

Customer,

Sales,

Date

Resident Sales;

Drop table Sales;

Regards,

Bhargav