Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dimensions with fixed min/max

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

11 Replies
sushil353
Master II
Master II

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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?

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Not applicable
Author

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".

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about using a calculated dim of:

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

-Rob

Not applicable
Author

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)