How to show zeros for related table

[Ormesome]

I have 2 tables: PRODUCTS and SALES. Each product can have zero or many sales, with each sale having a date, quantity, and value at the time of sale.

I want to create a sheet with filters so that I can show a table of the number and value of sales by product. I want to be able to show when a product has zero sales - this is rather important.

I also want to be able to apply filters to the products, such as brand.

Finally, I also want to be able to filter by date of sale but again showing all products that had zero sales in that time period.

I have tried the following measure

=SUM(sales_quantity)+SUM({1}0)

but while this gives me zeros for products without sales, it ignores all the filters.

[Tomm]

This might be a data model issue, as what you're trying to achieve appears to be default behaviour.

I dummied up a Product table with 4 products, and then a Sales table with 10 rows but with a certain product absent.

Do you have a QVF you can share?

[Ormesome]

Thank you.

Upon revisiting this I can see that products without sales report a sum of sales of 0. I also see that I can filter by product attribute correctly.

When I select the sales date range it automatically filters the product list to only those with sales in the period. Is there a way to list all products so that I can see the 0s with the other data?

[Qrishna]

share some sample data.

[Ormesome]

I have invented the following data:

Products:
Load * Inline [
ProductCode, ProductName, ProductType
A0011, Blue Pen, Pen
A0012, Black Pen, Pen
A0013, Red Pen, Pen
A0021, HB, Pencil
A0022, 2H, Pencil
A0023, 4H, Pencil
A0024, 2B, Pencil
A0025, 4B, Pencil
];

Sales:
Load * Inline [
ProductCode, SalesDate, SalesVolume, SalesValue, SalesType
A0011, 20241001, 1, 5, Cash
A0011, 20241002, 1, 5, Cash
A0011, 20241003, 1, 5, Cash
A0011, 20241004, 1, 5, Cash
A0011, 20241005, 1, 5, Cash
A0011, 20241006, 1, 5, Cash
A0012, 20241001, 1, 5, Cash
A0012, 20241002, 1, 5, Card
A0012, 20241003, 1, 5, Card
A0012, 20241004, 1, 5, Card
A0012, 20241005, 1, 5, Card
A0012, 20241006, 1, 5, Card
A0013, 20241001, 1, 5, Cash
A0013, 20241002, 1, 5, Card
A0013, 20241003, 1, 5, Card
A0013, 20241004, 1, 5, Card
A0013, 20241005, 1, 5, Card
A0013, 20241006, 1, 5, Card
A0021, 20241001, 1, 5, Cash
A0021, 20241002, 1, 5, Card
A0021, 20241003, 1, 5, Card
A0021, 20241005, 1, 5, Card
A0021, 20241006, 1, 5, Card
A0022, 20241001, 1, 5, Cash
A0023, 20241001, 1, 5, Cash
A0023, 20241002, 1, 5, Card
A0024, 20241005, 1, 5, Card
A0024, 20241006, 1, 5, Card
];

Without filters I can clearly see that there were no sales for the 4B Pencils:

A sheet with no filters applied

I'd like to apply a SalesDate filter of 20241006 and I'd like to see the zeros so that I can see which products did not have any sales:

The same sheet, but with a filter applied on the sales table

I'd like to be able to apply filters to the products table and see the zeros in the sales table so that I can see which dates (20240106) didn't have any sales.

A filter applied to the products table

In this way I'd like to be able to see for any given SalesDate period which products had sales or not, and then be able to drill down into the products themselves to identify which days didn't have sales.

[Tomm · Accepted solution]

I see. There are no [Sales] 'SalesDate' for zero sales product. You could change your [Product] table to be [Date | Product] table that has a row for every product on every date between min and max [Sales] 'SalesDate'. Then join to the [Sales] on a [Date | Product] key.

Rough code below - you may need to adjust around the edges. Run this after your tables.

Measure_SalesDate:
LOAD 
	MAX(SalesDate) as Max_SalesDate
    ,MIN(SalesDate) as Min_SalesDate
Resident Sales; 

LET Max_SalesDate = PEEK('Max_SalesDate',0,'Measure_SalesDate');
LET Min_SalesDate = PEEK('Min_SalesDate',0,'Measure_SalesDate');

LEFT JOIN(Products)
LOAD DISTINCT SalesDate+IterNo() as SalesDate
RESIDENT Sales
While $(Min_SalesDate)+IterNo()<$(Max_SalesDate);

DROP TABLE Measure_SalesDate;

Sales_Final:
LOAD *, AutoNumberHash256(ProductCode,SalesDate) as Product_Date_Key RESIDENT Sales; Drop table Sales; Drop Field ProductCode, SalesDate from Sales_Final;
Products_Final:
LOAD *, AutoNumberHash256(ProductCode,SalesDate) as Product_Date_Key RESIDENT Products; Drop table Products; 

[Ormesome]

I'm giving this a test now.

Doesn't this get rather messy when we also want to track purchases, store transfers, etc?

[Tomm · Accepted solution]

There are neater code solutions that would be better at scale - but without understanding how your data is managed hard to recommend. Essentially, you're needing an event table that stores your main date concept and has every relationship (Product, Sale, Store, etc) against that date. 

I would model this into a star schema in a stand-alone app and store the resulting tables as QVDs, then do my reporting off that layer in a second app. 

Do you have a date_table that you can leverage instead of generating the range of dates (as I did in my code above)?

[Tomm]

I forgot to include that you should ensure your SalesDate is a date datatype - you can do that like this 

DATE(DATE#(SalesDate,'YYYYMMDD'),'YYYYMMDD') as SalesDate

nb it may already be if the data you provided was a dummy you put together.

[Ormesome]

In summary, I wish that there was a neater solution than "create a massive fact table where zero is a fact" but this does the job.