barchart- date axis, show bars when counts are 0 (pic included)

[ioannaiogr]

Hi all, 

this is a master calendar date field. I picked dates from 1/8/2021 to 1/9/2021

and the barchart showed only the dates when information was present. I did" label values" to show the counts.

Is there a way to show the bars with label 0 for the gap dates where there are 0 counts?

Thank you in advance!

[AshutoshBhumkar]

Hi,

Just check whether you have unchecked the 'Include 0 Values' under Add-ons tab.

[ioannaiogr]

Hello @AshutoshBhumkar , yes it is checked already!

[AshutoshBhumkar]

Take a straight table and measure to check if all dates are present and its data.

Could you please post any screenshot of data ?

[ioannaiogr]

Hey @AshutoshBhumkar , did so, as you can see in the straight table it's correct.

My data model is pretty simple, it's just

table1:

id1,

date(floor(date),'DD/MM/YYYY') as date;

table2:

id2

id_fk as id1,

field;

and date is a master calendar field

in the chart x axis is date

and y axis is measure count(distinct id1)

[AshutoshBhumkar]

Hi,

Not sure about Master calendar in your script. Check below example.

Data:
LOAD
Division,
Region,
Sales,
"Date"
FROM [lib://AttachedFiles/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1);

MasterCal:
LOAD

Date(Date) AS Date,
Month(Date) As Month;
Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load
Min(Date) AS MinDate,
Max(Date) AS MaxDate

RESIDENT Data;

Output:

[ioannaiogr]

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min( [Date]) as minDate,

max([Date]) as maxDate

Resident Data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[Master Calendar]:

Load

TempDate AS [Date],

week(TempDate) As StudyWeek,

Year(TempDate) As [Yeae],

Month(TempDate) As [Month],

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,

WeekDay(TempDate) as DayOftheWeek,
week(TempDate) As WeekTest,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYearTest

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

 @AshutoshBhumkar , my script for master calendar. Do you think here lies my problem? Why? I'd like to learn 🙂

[Digvijay_Singh]

Sometimes the trick of adding  + Sum({1}0) in the measure expression works but it also depends on the dim/measure fields used and the associations in the data model..

[ioannaiogr]

Hi @Digvijay_Singh , tried this and then the selections in the date filter offer nothing. My model is as showed above, simple as that.

[Digvijay_Singh]

Can you share your bar chart image, dim/measure expressions?