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. 

[Tanish]

Hi Ricky,

Rather than using the Table, if you will use Line chart it will display all the dates including where sales has not happened.

[RickyLam]

---

@marcus_sommer wrote:

 .... just add beside your expression of sum(Sales) another expression with something like: = 1

---

What do you mean by that? Do you mean in the same expression editor, I type the following? 

=Sum(Sales)

=1

[RickyLam]

Thanks; but for me both line chart and table are just two different visualisations for the same set of underlying data? Why do they behave differently for "missing" dates? 

[Tanish]

Hi Ricky,

We have a option of showing the missing values in the line chart which is not present in table. Try this option it will help...

Thanks

Tanish 

[marcus_sommer]

I meant to use at least two expressions (best would be within a table-chart) and one would be the normal aggregation-expression which you want to use and the other one is aimed to plot a fixed and to the data-model unrelated value of 1 or maybe also 'x'.

[marcus_sommer]

The data-processing logic is for all charts the same and it's no coincidence that by creating a new object you have only one choice to create a chart and after choosing it you could define which type you want to use. So the underlying logic is always the same.

Nevertheless have the different types different features and properties. In lack of much experience with Sense I'm not sure to which property @Tanish is referring but in QlikView were an axis-option to enable a continuous axis which might be able to fill such data-gaps - whereby I don't use them because there were always more disadvantages as benefits and therefore I haven't much experience with it.

If an object offers such possibilities it doesn't mean that the working itself is different else that on top are extra measures integrated - and in this case it might be just something like I described above with an extra (hidden) expression of: = 1 to bypass the facts-relation of the expression. Another way to provide such views may be that it just takes the min. and max. values of the dimensions and adds simply 1 to the min. value until it reached the max. value. How ever such measure is implemented it will have side-effects whereby they may for your scenario be working and be the most practically approach - therefore just try it. 

[RickyLam]

@marcus_sommer 

Sorry for my late response, but I am looking into your suggestion about inserting calendar dates for dates with no sales based on your sample code:

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

But now I am facing a difficulty... Unfortunately our model is not that sample. 

The sales data are actually stored in the OrderDetails table, which is related to Orders table by a common field called OrderID, and Order table is related to the master calender by a command field OrderDate. So OrderDetails table is not directly related to the OrderDetails table. 

My initial code is something like this, but it does not seem to work:

MasterCalendar:
// Note: It is given that TempDate is a field containing a list of consecutive dates
Load TempDate as OrderDate, TempDate as calDate

Quality *;
Unqualify OrderID;
Orders:
Load SQL <SQL Statments>>
load calDate as OrderDate, 'xxxxx' as OrderID
resident Calendar where not exists(OrderDate, calDate);

Qualify *;
Unqualify OrderID
OrderDetails:
Load SQL <<SQL Statements>>
Load 'xxxxx' As OrderID, 0 as LineTotal, 0 as LineQty

It seems to me that, as OrderID is the linking field between the two tables, Qlik Sense does not allow me to manually insert values into it. 

Please help review the code above and advise how to improve it or suggest any alternative actions. Thanks a lot.

[marcus_sommer]

I wouldn't load these tables in this way else merging them with a join or shouldn't the data have an 1:n relation switching to a mapping-approach to prevent any risks of changing the number of records.

I know it's a common data-base structure and they made sense within the SQL world but Qlik doesn't used a relational data-model else an associative one and therefore such measure will have a lot of benefits within Qlik. We haven't such order and order-detail structure within our company and therefore I tried some years ago to rebuilt it and created an artificial split of them. But it was a bad idea - not only the efforts to get this split else much more with them. A lot of challenges came which were of course solvable but the performance drop was significantly and even more worse were the efforts to maintain it within a multi-tier data-architecture.

Beside of this the above example to add records were simplified and you might need to include some more fields and for each required Key (category, channel, ...) an own missing date - not mandatory to the lowest granularity but more as just few records are possible.

[RickyLam]

I understand that retaining Order - OrderDetail design is not a good idea; yet because there has been lots of visualisations created in the same QS app has been based on this structure, merging them into a single table at this moment would imply reconstructions of most of these visualisations...

So, given that the Order and Orderdetails table are to be stayed in the QS app, do you have some other ideas to deal with my issue? Thanks. 

[marcus_sommer]

Making such essential design changes afterwards is not always possible respectively sensible. Better is often to collect all the experiences and applying it within the next business-release - which might be already on the road-map.

If you need to remain by your current data-model - and also by the plan to provide a NULL analysis (see also my above comments about the usefulness) - you will need to add missing values to both tables. Before going in this direction I suggest to review the requirements again if not such simple method like adding a fixed expression of 1 or any string like '' might be sufficient to get the wanted information. Especially if it's not a daily reporting for many else in the direction of a monthly reporting for certain users and - like in our company - that it's quite common to have a lot of derived Excel exports and PDF prints from Qlik to provide it also in this way.