how to display not existing values (dates) in chart

[#1]

How can I display days without sales in Chart.

For example, we take 1 month with sales, but we sale product not every day.

How can I create a Chart with 0 value where there where no sales?

see attached file

[#2]

In the tab Presentation, at the bottom is the option to change Null to 0.

Hope it helps!!

[Anonymous]

In the dimension tab, you need too change property, to show all values.

Henrik

[#4]

Henrik and javierortiz, not the solution

[Anonymous]

You also need a table with All the dates (a Master calendar). And it must be linked to your fact table.

Henrik

[hic]

You need to populate the missing cells with zeros.

Data1:
LOAD date,
sales,
product
FROM [демоданныепродажи.xlsx] (ooxml, embedded labels, table is Лист1);

// ============= Find Min Date and Max Date
MinMaxDate:
Load Min(date) as MinDate, Max(date) as MaxDate resident Data1 ;
Let vMinDate = num(floor(peek('MinDate', -1, 'MinMaxDate')));
Let vMaxDate = num(floor(peek('MaxDate', -1, 'MinMaxDate')));
Drop Table MinMaxDate;

// ============= Create cartesian product
CartesianProduct:
// 1) All dates
Load distinct Date($(vMinDate) + recno(),'YYYY-MM-DD') as date autogenerate $(vMaxDate) - $(vMinDate);
// 2) All products
Left Join
Load distinct product resident Data1;

// ============= Join Cartesian product onto Data table
Outer Join (Data1) Load * resident CartesianProduct;
Drop Table CartesianProduct;

// ============= 2nd pass through Data table - populate missing combinations
Data2:
NoConcatenate
LOAD
product,
date,
if(Len(Trim(sales))=0,0,sales) as sales
RESIDENT Data1;

Drop Table Data1;

[#7]

The main idea is to do it without adding 0.

[hic]

Then there are three settings that you need to change:

* Properties - Dimensions - Show all values

* Properties - Presentation - Suppress zero values

* Properties - Presentation - Suppress missing

But there are still cases when the chart does not show exactly what you want...

[CELAMBARASAN]

Hi,

     As of me in chart you can display the values which you have in that dimension column only.

     So you have to create a calendar table which has all the dates from the start to end.In this, date field name should be same as the date field you used in sales table.

     Then use expression as Alt(Sum(sales), 0)

     uncheck the suppress zero values and check the zero on bars option in chart Properties-->Presentation tab.

Celambarasan

[jagan]

Hi,

If you want only not existing values then try this

Try this expression

If(Alt(Sum(sales), 0) > 0, 0, 1)

Now it displays only non existing values with 1.

Regards,

Jagan.