Master Calendar Usage Issue

[RickyLam]

Dear all,

I have created a master calendar which has two fields of identical contents:

PMT_ORDER.LAST_IMAGE_DATE, which is only used for associating with the order table, PMT_ORDER; and

calDate, which is created for creating chart expressions:

Now the issue is, while the calDate field is a list of consecutive dates, when it is used for creating chart expressions which inevitably involved other tables directly or indirectly related to it, only calDates with non-zero aggregated values are displayed like the following:

The left table, "Master Calendar" displays the consecutive dates of its 'calDate' in descending order. 

The right table, "Master Calendar Test", supposedly displays the daily sales amount for each calendar date.  As you can see, the dates of its calDate column is no longer consecutive because dates which have no sales are not displayed, as if the associations are of inner join, not the expected outer join.

What other steps do I need to take so that, in the Master Calendar Test table, calDate column always shows consecutive date, with no-sales date with Sales Amount displayed as zero? Please advise. 

[Arek92]

Hi,

Have you tried in your table options to get to Add-ons, then to Data handling and tick "Include zero values" option?

[marcus_sommer]

It's not really related to the master-calendar else to the missing dimension-values within the fact-table. And this is also the expected and intended behaviour - if no facts exists it's nothing there to show. This means this isn't a bug or an error else it's wanted and correct. And not only in Qlik else all other tools behave in the same way - at least within the defaults.

Of course there are workarounds to force such views without having originally data but this comes always with more or less disadvantages. A quite simple way would be to populate the missing data within the data-model, for example with an approach like:

concatenate(FactTable)
load CalendarDate as FactDate, 0 as Sales
resident Calendar where not exists(FactDate, CalendarDate);

which simply added the missing data to the facts. Of course such measures will increase the dataset but if such logic isn't applied on the lowest granularity else on a date and maybe a product or a store it's mostly not too heavy. 

[RickyLam]

@Arek92 ,

"Include zero values" has been turned on already. Seems it is a default. But the zero-sales days still don't show up. Please advise further. 

Ricky Lam

[RickyLam]

@marcus_sommer ,

Thanks for your quick response. By the way, may I digress a bit further.

One of the aspects I worry a lot is the computation of daily sales averages. In our system, sales events do not occurred on every calendar days, but when it comes to daily sales average computation, every calendar days must be counted. 

Suppose we have a OrderItem table indirectly related to the master calendar via the Order table. If the daily sales average expression is something like that:

=SUM(OrderItem.LineTotal) / COUNT(MasterCalendar.calDate)

and suppose we have another dimension called "Month" and one of the values is "July". In July only 25 out of 31 days have sales. My question is, what will be the result of COUNT(MasterCalendar.calDate)? 25 or 31? In this case I want 31.  

Please advise further. Thanks a lot in advance. 

[marcus_sommer]

Beside the above mentioned approach of populating the missing data you may in regard to period-data consider to add such information within the calendar. This means adding continuous counter for calendar- and working-days/weeks/month per per year/month and so on (could be done per interrecord-functions and/or autonumber() approaches) as well as adding the max. number of them (maybe per self-joined aggregation-loads) and also various kinds of flags for YTD, MTD ... and then you pull just the wanted value from the calendar.

Another method might be to count not against the current dimensionality else against a higher level, for example per: count(distinct TOTAL DateField). If there are really no sales for anyone on Sundays they would be further not counted but in regard to get a sensible and over periods comparable average it might be practically enough ...

[RickyLam]

Thanks for your response, but it seems rather complicated to me as a newbie...

If I could get you, suppose I created a field called "calMonth" as calendar months in my master calendar, and used this field as a dimension. If this dimension is used to partition the measure:

=SUM(OrderItem.LineTotal) / COUNT(MasterCalendar.calDate)

would the denominator of this measure returns the number of calendar days of a particular month? Please advise further.

[marcus_sommer]

No, this won't work because the fact-date and the dim-date are associated and a NULL respectively NOTHING on ones side will have an impact on the other side and even more important in this regard the measure-field comes from the fact-table - and so there is an association against NOTHING which couldn't lead to display anything to this dimension-value. Like above hinted this is the normal behaviour.

Of course there are various workarounds to bypass such scenarios. But none of them is trivial and many will have serious side-effects. Therefore I suggest to consider if this what do you want to do is really expedient in regard to the aim behind it. The business queries often such zero-views but which added value could they derive respectively which sensible measures? In my experience such things may be suitable for a fine-tuning - optimizing the last few percent of whatever by relative high costs - and mostly they are barely capable to keep the main-processes working. I did such ***bleep*** stuff already several times and it was NEVER used. In this regard you may to consider to discard the matter or at least to move it to a later point of time ...

Nevertheless if you want to continue with this matter I recommend to remain by the above mentioned approach because it's the most simple one. Adding the missing dimension-values isn't too hard, the extra number of records is often not really noticeable and everything else in the UI remains by the standard. All other ways to enforce anything within the UI is much more complicated and comes with serious disadvantages to the user-experience and/or the performance.

Here some more backgrounds to NULL and the hinted methods of creating a calendar and the generation of data:

NULL handling in QlikView - Qlik Community - 1484472

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

Generating Missing Data In QlikView - Qlik Community - 1491394

[RickyLam]

While I think adding missing dates with zero sales to fact tables, i.e. Order and OrderItem tables, would be the direction to go, still I want to clarify the way "associations" work in Qlik Sense.

As far as I know, when two tables have the same field name, two tables would be "associated" like a full outer join, right? So when the master calendar is "associated" with another fact table like "OrderItem", all records (hence all days)  in the master calendar would be listed, and dates not present in the fact table would be inserted as "NULL".. But based on what you said, if a date in the master calendar which does not have a related record in the fact table would not be shown, then is it still an "outer" join? 

[marcus_sommer]

It's indeed a full outer join. But only if you look on pure table-data. As far as you used a chart with aggregations the working-method changed slightly because all included fields from dimensions and expressions are now considered.

The visible dimension-values aren't fixed else they will be defined by the expression-results. A very good example for this intended behaviour is a simple selection. If you select a single date you won't further see all dates with zero-results in the expressions unless the selected ones else all non-possible dates will be excluded as visible dimension-values.

By missing dates in your facts it's similar. There is no possible value available against the queried Sales and therefore nothing is shown. In this regard I do remember a very simple method to enforce the view of all possible dimension-values which has of course (more or less acceptable) side-effects to the view but it should demonstrate the underlying logic quite well: just add beside your expression of sum(Sales) another expression with something like: = 1