Show zero measures in a graph

[Markbhai]

(Apologies, this was posted earlier but was my post was so full of flaws, I removed it).

I am trying to show a graph of tickets raised over a periiod of time.  In my data I have zero 'Open' tickets in October and as a result October does not show on my graph.

I have tried a 'Master Calendar' which works until a filter is applied.  -

I have also read about adding + sum({1}0) to my measure.

Count({<[Status]={'Open'}>}id) + sum({1}0)

and setting the Addons -> Data Handling -> Include zero values 

Any help would be appreciated

[igoralcantara]

The correct approach is the Master Calendar. In addition, check if the Add-Ons property "Include zero values" is active.

Additionally, can you give more details of how the master calendar was created and what sort of selection breaks it?

[Markbhai]

Thank You for your help I am using the following script:

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

min([Reported Date]) as minDate,

max([Reported Date]) as maxDate

Resident foi;

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);

MasterCalendar:

Load

TempDate AS [Reported Date],

week(TempDate) As Week,

Year(TempDate) As Year,

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 MonthYear,

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

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

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

This will show the table correctly:

However when I click on any other graph the resulting graph does not show blank months.

Any help grefully appreciated.

[igoralcantara]

oh, I see what you're saying. First, check if the master calendar links correctly to the rest of the data model. Do you link by [Reported Date]? Maybe reporter date in your "Fact" table has timestamp on it and on the calendar only the date. Even if does not show in the table preview, this might be true. Using floor on both fields and then reformatting back as a date make work.

So, in the Data Model Viewer, check for both Subset ration. If they add up to 100% or close, that could be the case.

If your link works, you might need a link table. Let me know if that is the case and I can give you an example on how to create one.

[igoralcantara]

Just adding an example of what I say to convert to just date. Don't use just the date function. It will not change the data type in the field, it will simply mask it. Do something like this:

Date(Floor([Reported Date]), 'MM-DD-YYYY') As [Reported Date]

[Markbhai]

Thanks Igor.

Yes I am linking by Reported Date.  I thought we might have been on to something there as the dates were showing as with time in the foi table and without in the Master Calendar table.  Now I have them both showing as date:

 

In terms of the Data Model we have this:

FOI

Master Calendar

Thanks once again.

Mark

[igoralcantara · Accepted solution]

Okay, let's try doing a Link Table then. This is an example, adapt to what you need.

1. Create the Link Table Script: Let's start by creating a Link Table script that includes all unique dates or months from the MasterCalendar table. We'll use the `CROSSTABLE` function to achieve this. Here's the script:

LinkTable:
CROSSTABLE (MonthType, MonthValue)
LOAD
Date as MonthValue,
'Date' as MonthType
RESIDENT MasterCalendar;

Concatenate (LinkTable)
LOAD
Month as MonthValue,
'Month' as MonthType
RESIDENT MasterCalendar;

This script creates a table with two fields: `MonthType` and `MonthValue`. The `MonthType` field specifies whether the value is a specific date or a month, and the `MonthValue` field contains the corresponding date or month value.

2. Link the Link Table to MasterCalendar and FactTable: Link the Link Table to both the MasterCalendar and the FactTable using appropriate key fields. Here's an example of how you can do this:

LinkTable:
LOAD
MonthType,
MonthValue
RESIDENT LinkTable;

LEFT JOIN (LinkTable)
LOAD
Date,
Month,
Year
RESIDENT MasterCalendar;

LEFT JOIN (LinkTable)
LOAD
Date,
FactField1,
FactField2,
... // Add other fields from FactTable as needed
RESIDENT FactTable;

Replace `FactField1`, `FactField2`, etc., with the actual fields you have in your FactTable that you want to link with the MasterCalendar.

3. Reload the Script: Once you've added the above script to your Qlik Sense app, reload the script to create the Link Table and establish the associations.

Let me know if that helps you.

[Markbhai]

Thanks Igor

I havent been able to have a go at this yet on account of me finishing work, but by FactField1, FactField2 etc do you mean the fields which I am likely to select on the dashboard which will filter my table? eg [Status] for Open/Closed tickets etc?

Thanks Mark.

[igoralcantara]

Yes, they should go in the link table. There also a few youtube videos that explain this in more detail.

[Markbhai]

Thanks Igor,

This works and I have marked it as the solution.

I dont think though that it will be practical for my needs as the resulting table is 9m records long (and I have only added a few fields).  If I were to expand this across the data base I suspect it would end up being too resource hungry.

I guess I will just have to accept that I can show dates with zero values.

Thank you so much for your help.