Layout - display all dates in dimension, without data?

[datanibbler]

Hi,

in one of my charts, I have to rely on an Excel file populated by another dpt. and the date is unfortunately populated only up to today. I talked to the responsible, but apparently the formulas would break otherwise.

In my other diagrams, I make use of just one big Excel file that has places for lots of different data - but the date (or for some things only the month) is always populated until December, with some formula errors and lots of empty space.

For all of these diagrams, when i select a certain week, I always have six days on the x-axis, even if there is no data yet.

That makes for a uniform appearance, the columns always have the same width.

In my new diagram I would like to have the same. Is there any way to achieve that? The checkboxes "Show all values" and so on don't do it.

Background: I have a calendar with all 365 days, the date_field of which I use to link most tables to it. The same with this one - but I load the date_field twice, once as >Date_HR< and once as >%Date<. It doesn't matter which one I use as a dimension, I get only those days for which I already have data.

Thanks a lot!

Best regards,

DataNibbler

[a_mullick]

Hi,

I believe that you need to generate the missing data in the load script. See if this document helps:

http://community.qlik.com/docs/DOC-3786

Thanks,

Azam

[datanibbler]

Hi Azam,

this certainly does help. I will have to use the same logic as in my mastercalendar to autogenerate the missing date_values until december (or, if I can make it, only until the end of the current week, nobody will have the idea of looking for data in a week to come...). It should work then.

I have another scenario where this might be doable, but would probably make the table too big: A personell masterdata table with a starting_date and a leaving_date for every employee. I will instead try to use the interval_match function "upside-down" to determine whether an employee was with the company in a given period.

Best regards,

DataNibbler

[datanibbler]

Hi Azam,

I have now created a very small calendar - vMinDate is TODAY() and vMaxDate is this Sat. The logic is the same we already have in two mastercalendars, I just copied the commands.

That does not seem to work yet, however. In the underlying list, there is data up to (yesterday). I should this see two more days on the x-axis: Today (Friday) and tomorrow (Saturday).

I will try some more, but if you can give me any more help here, that would be great.

Thanks a lot!

Best regards,

DataNibbler

[a_mullick]

Hi,

Sorry for the delay, but I've been away on holiday

Is this still a problem? I'm not quite sure exactly what your data looks like, if you can send me an example of what you have and what you want to see, it would make it easier to visualise. But one possible simple solution is to uncheck the Supress Zero-Values in the presentation tab. In the sample below, I have no facts for the 3 and 4 Feb 2013 (even though the date exists in my calendar dimension), but the dimension is still displayed.

Azam

[datanibbler]

Hi,

I was away myself. I just returned this week. So excuse me for the delay in my turn.

Unfortunately, I cannot send you a sample of the original data as personell data is a very sensitive issue in Germany. It actually looks like a crosstable - the date goes from left to right and all the categories are in the rows.

I could not load it that way, though, because there are several table_areas on that sheet and there would have been duplicate fieldnames with different values. So I used an alternative load statement. I will attach that anyway.

=> I load all the data from the file, but as you see in the Screenshots, I display only a part of it - dependent on a variable which, in turn, is controlled via two selection_boxes. (I attach two screenshots, one with the diagram as it is now and one with what I would like to see)

For some reason, the date is only populated together with all the data - daily.

In the data_model, the table is linked to a master_calendar via a field >%Datum<.

What makes the thing so strange is the fact that in other diagrams, I have actually the same scenario: The date is populated only up to the last day with any data, I have loaded that field twice (once original, once as the key_datefield) and linked it to the master_calendar - and it works: In the dimension, all six days of a week are displayed, even when there is no data.

Thanks a lot!

Best regards,

DataNibbler

[a_mullick]

Hi,

See the attached .qvw. I can reproduce the problem by using the Date_HR as a dimension and fix it by using %Datum as the dimension. Have a look and see if this works for you…

Azam

[datanibbler]

Hi Azam,

thanks! Unfortunately, that does not work in my instance - I have unticked those checkboxes to "hide null-values", I could detect no further difference between yours and mine - except I have two dimensions, dependent on a variable v_show_per_day, that is used to switch between a per_day and per_month display - but that should not make any difference, should it?

I can try deleting one and removing the condition, that's not much of an effort.

P.S.: Oh, cool - now I've found it. There is another checkbox "hide null-values" on the "presentation" tab. That did the trick. Finally. That will make the diagram a lot nicer to look at as the bars are now all the same width on Friday as on Monday 😉

Thanks a lot!

Best regards,

DataNibbler

P.P.S.: Actually, I realized I have to tick the checkbox "hide null-values" on the "dimension"-tab, otherwise my dimension (with a condition to exclude Sundays) would return six days and a '-' on the x-axis.

That was my breakthrough for today. I guess I'll wrap it up now.