topic Re: Dimensions with fixed min/max in QlikView

Dimensions with fixed min/max

Mon, 11 Mar 2013 13:42:34 GMT

Hi. I have a table with date-values in this format:

yyyymmddTHHmmdd

Now I would like to have this in a "Line Chart" as "Dimension"(x-axis) with only the dates. I am doing this by setting me Dimensions to the following expression:

left(dateStr,8)

This works fine but my problem is that objects that do not have a any of the dates are not displaying this "empty" dates. What I would like it to display is to show all dates and make a zero indication when a date is not pressent.

Any idea how I can make my "Line Chart" to display max/min values in x-axis and make the graph move to "0" when a date is not pressent?

I have tried "Chart Prop/Axes/Dimension Axis": Scale:

Static Min:

Min(left(dateStr,8))

Static Max:

Max(left(dateStr,8))

But still: Not present days are not presented. Thought I have to make e new ListBox with only all dates but dont know how and that would raise some more problems. Any tips or ideas?

Re: Dimensions with fixed min/max

sushil353 — Mon, 11 Mar 2013 13:52:01 GMT

Hi,

Try to set the date format in edit script initials according to your date format.

SET DateFormat='yyyymmdd';

SET TimestampFormat='yyyymmddTHHmmdd';

by doing this you will get your data in date format inside qlikview. and then you can eaisly use date functions in qllikview.

Hope this will help you

Sushil

Re: Dimensions with fixed min/max

Gysbert_Wassenaar — Mon, 11 Mar 2013 13:55:16 GMT

Try unchecking the Supress Zero-Values on the Presentation tab of the properties window. Also you should consider making a date from the datestring in the script: date#(left(datestr,8),'YYYYMMDD')) as MyDate

Re: Dimensions with fixed min/max

Mon, 11 Mar 2013 15:18:02 GMT

Ok. Unchecked the Supress Zero-Values on the Presentation tab.

Made a date in the script like this:

Table:
LOAD date#(left(dateStr,8),'YYYYMMDD') AS MyDate
FROM  xmlFile.....

Problem with this is that it is not connected with my objects in any way. For it to be useful I would need to be adding in "Dimensions" with "dateStr" somehow.

Right now my "Dimension" for the chart looks like this:

=Aggr(left(dateStr,8),dateStr)

Need some kind of "if statement" in my charts dImension-expression?

Re: Dimensions with fixed min/max

Gysbert_Wassenaar — Mon, 11 Mar 2013 15:27:49 GMT

Well, you could just name that field dateStr instead of MyField. That was just an example. Whatever you name it, it's the field you need to use everywhere instead of the string field dateStr. Calculations on strings are a lot slower than calculations on numbers. So don't use dates that are strings.

Re: Dimensions with fixed min/max

Mon, 11 Mar 2013 15:40:31 GMT

Consider also looking at the date functions; see if monthname() or datename() might return the correct value for you. QV has a lot of built-in functions that can read even mixed date/time values.

Re: Dimensions with fixed min/max

Mon, 11 Mar 2013 15:52:52 GMT

I can see the "MyDate" was just example. But I dont understand how to replace this with my existing "dateStr" because "dateStr" is connected to other objects. But the "MyDate" seems to be unconnected with my other data when loaded with "date".

Re: Dimensions with fixed min/max

Gysbert_Wassenaar — Mon, 11 Mar 2013 17:37:48 GMT

Please post a sample document. If necessary you can use scramble the contents of fields to make sensitive data irreversibly unreadable (unless you reload from source data, which you can do and we can't). On the Scrambling tab of the document properties window you can select the fields to scramble. You can select some values and choose File->Reduce Data->Keep possible values to reduce the data and thus size of the document.

Re: Dimensions with fixed min/max

rwunderlich — Tue, 12 Mar 2013 05:00:49 GMT

How about using a calculated dim of:

=if(IsNull(Date),0,left(Date,10))

-Rob

Re: Dimensions with fixed min/max

Tue, 12 Mar 2013 07:56:38 GMT

Thanks for the scramble info!

For some "Room" the full x-axis is not displayed(i.e. EH7721),

and for some are non-existing values the same as other values(i.e. WV6446)

Re: Dimensions with fixed min/max

Gysbert_Wassenaar — Wed, 13 Mar 2013 06:49:30 GMT

Ah, you also don't have a field dateStr. As said before, I think you should create dates from the strings in the script. I don't see any problem with begin being connected with other tables. If I look at your data model in the table viewer I see that begin is not a key field, so there are no connections.

reservation:

LOAD id,

date#(left(datestr,8),'YYYYMMDD')) as begin,

date#(left(datestr,8),'YYYYMMDD')) as end,

date#(left(datestr,8),'YYYYMMDD')) as modified,

date#(left(datestr,8),'YYYYMMDD')) as created,

%Key_reservations_15C6F86815BA58DF, // Key to parent table: reservations

%Key_reservation_EBD059C931B3FB51 // Key for this table: reservations/reservation

FROM Z:\Downloads\TEStat\TEStatReport_20130308T162341.xml (XmlSimple, Table is [reservations/reservation]);

After you've done that replace the calculated dimension in the chart with just the field name, e.g. begin.

Re: Dimensions with fixed min/max

Wed, 13 Mar 2013 07:07:14 GMT

Aha, when loading all tables again in the same load they got linked together. Thanks!