11 Replies Latest reply: Mar 13, 2013 3:07 AM by grains85 RSS

    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?

        • Re: Dimensions with fixed min/max
          Sushil Kumar

          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

            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

                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

                    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

                        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

                            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

                                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

                                    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

                          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
                            Rob Wunderlich

                            How about using a calculated dim of:

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

                             

                            -Rob