8 Replies Latest reply: Mar 28, 2011 8:34 AM by gauravv2010 RSS

    Input Box to control Line Graph

    HeyZeusChris

      Hi I want to have two input boxes, where a user can enter a start date and an end date. The Line graphs that I have will then update So that the values displayed are always in between the start date and the end date.

      Can this be done? If so, how?

       

      Here are some more details:

      I am using Time group as my x-axis (year, quarter, month day). The Y axis is a some of sales (sum(sales))

      I have created two input boxes that use the variables called StartDate and EndDate.

      Not sure where to go from here.

        • Input Box to control Line Graph
          John Witherspoon

          On the Axes tab, set the dimension axis to continuous, set the static min to StartDate and the static max to EndDate. The only problem I've had with that is sometimes it doesn't preserve your date formatting, so a month, for instance, might instead show as the first date of the month, or however you have it defined internally.

            • Input Box to control Line Graph
              HeyZeusChris

              Hello Mr. John Witherspoon,

               

              I am having trouble making this work.

              Here are some details.

               

              I have a Quarterly graph. It uses a group called "Quarterly Time". This group uses the following fields : QuarterLabel, MonthLabel, Day.

              These here is the definition of these fields that are loaded from a SQL table:

               

              SQL SELECT

              CAST(Datepart(m, Date) as varchar(2)) + ' - ' + RIGHT(Year(Date),4) as MonthLabel,

              'Q' + CAST(Datepart(q, Date) as varchar(1)) + ' - ' + RIGHT(Year(Date),4) as QuarterLabel,

              datepart(dd, Date) as Day

               



               

              I do this to make the labels of the axis nice. My select statement has an ORDER BY Date Clause to so that I can define the Group Sort Order by the Load Order. This seems to work pretty well.

               

              Doing what you suggested did not work for me. It just made a blank graph that displayed the error message "No Numeric Values on x-axis"

               

              I think there must be a better way for me to load my data, label my axis, and define my variables so that what I am trying to accomplish will work. I look forward to your input.

               

              -Jehovah

               

                • Input Box to control Line Graph
                  John Witherspoon

                  One likely problem making it work is that you have text strings instead of dates as your date fields. Instead of building labels in the SQL, over on the database side, load ONLY the date from SQL. Then build a calendar table, and use actual date fields in it rather than text strings.

                  Exactly how to generate a calendar can be specific to each application. But as an example, lets say you know your data starts January 1, 2010 and ends today. You could build a calendar table like this (untested):

                  Calendar:
                  LOAD *
                  ,date(yearstart(Date),'YYYY') as Year
                  ,dual('Q' & ceil(month(Date)/3) & '-' & year(Date),quarterstart(Date)) as Quarter
                  ,date(monthstart(Date),'MM-YYYY') as Month
                  ;
                  LOAD date(makedate(2010,1,1)+recno()-1) as Date
                  AUTOGENERATE today()-makedate(2010,1,1)+1
                  ;

                  Your group would be Year, Quarter, Month, Date. Sort each of those fields by numeric ascending. No reason to do an order by in the database unless you need it for some other reason.

                  But now I'm thinking the continuous axis isn't what you're asking for or need.

                  If you only want that one chart affected by the variables, you could use set analysis. Let's say your chart had an expression of sum(Sales). Replace it with (untested):

                  sum({<Date*={">=$(StartDate) <=$(EndDate)"}>} Sales)

                  If you want the entire application affected by the variables, you'd probably want to use an action to select dates in that date range, and trigger the action any time one of your variables is changed. I'd have to fiddle with it to get it working. I don't remember quite how to do it off the top of my head.

                    • Input Box to control Line Graph
                      HeyZeusChris

                      This is really helpful, and I think I am almost there. I can't thank you enough for your help!

                       

                      1) I think there might be a problem with the formula

                      sum({<Date*={">=$(StartDate) <=$(EndDate)"}>} Sales)

                      I don't understand the formula so I am not sure exactly what is wrong. Naively, I would guess there the '*=' part isn't right because when I edit the expression I get a red squiggly underline. Can you take a second look? Or better yet explain the logic of the formula?

                       

                      2) Also, Using the calendar table you recommended is working well. The only problem is right now my dates are showing as '8/1/2008 12:00:00 AM', which is cumbersome. Where can I actually format this so that it just shows '8/1/2008'?

                       

                      3) Is it possible to switch between the drill down levels without actually drilling into just one year, or quarter? To elaborate, is it possible for a user to see a graph of these sales by quarter for 3 or 4 years? Right now I can only see the quarterly drill down if I select just one year, say 2008. But a user might want to see the quarterly trend over the last 6 years, or a monthly trend for the past 3 years.

                       

                      I know this last item is possible if I set up 3 graphs. Each graph would have a unique group that it uses for the x-axis. One would have a group whose top level is Year. Another would have its top level as quarter, and a third its top level of Month.

                       

                      What are your thoughts on this?

                       

                      Thanks again!

                       

                       

                       

                       

                       

                        • Input Box to control Line Graph
                          John Witherspoon

                           


                          HeyZeusChris wrote:1) I think there might be a problem with the formula
                          sum({<Date*={">=$(StartDate) <=$(EndDate)"}>} Sales)
                          I don't understand the formula so I am not sure exactly what is wrong. Naively, I would guess there the '*=' part isn't right because when I edit the expression I get a red squiggly underline. Can you take a second look? Or better yet explain the logic of the formula?


                          The *= part is intersection. Normally people just use =, but that overrides any selections you've made, which I don't think is clearest for the users. As a user, let's say I use the two variables to select all of 2010. If I then select only May 21 2010 from a Date drop down, using Date= will ignore that selection, while using Date*= will respect that selection, and only show you May 21 2010. However, if you select May 21 2011, you'll get nothing, because the intersection of your selection in the Date field and your "selections" in the start and end dates is null. The rest of the formula is just saying that you want dates between the start date and the end date, inclusive. The $() part is called dollar sign expansion. What it should do is insert the values of your date fields into the expression. One possible problem would be if StartDate and EndDate have a different format than Date. QlikView is doing a text comparison, not a date comparison, so the formats have to match. If necessary, you could enforce this by replacing the $(...) parts with $(=date(...),'YY/MM/DD') or whatever format you need. Another possible problem is when OTHER date fields are in the application, and you've made selections that are in conflict with the range from the variables. The above is consistent, in that it will respect all selections, and if there's no overlap, it will return nothing. If you want it to ignore all date selections except for the variable, you'd need to do something like this:

                          sum({<Year=,Quarter=,Month=,Date={">=$(StartDate) <=$(EndDate)"}>} Sales)

                          When you use an = with nothing after it, it tells the system to just ignore selections in that field. So you list every date-related field like that, and then put the set on the Date field itself.

                          And of course I could have just made a simple mistake that's keeping it from working. Not seeing it, though.

                           


                          HeyZeusChris wrote:2) Also, Using the calendar table you recommended is working well. The only problem is right now my dates are showing as '8/1/2008 12:00:00 AM', which is cumbersome. Where can I actually format this so that it just shows '8/1/2008'?


                          Look at the top of your script. It sounds like you've somehow set your default date format like this:

                          SET DateFormat='M/D/YYYY hh:mm:ss TT';

                          When what you want is this:

                          SET DateFormat='M/D/YYYY';

                          There are plenty of other ways to change date formats though, so that might not be it. We'll start there, though.

                          Oh, hah. I just realized that that's probably the problem with the set analysis. Fix the date format, and the set analysis will probably work.

                           


                          HeyZeusChris wrote:3) Is it possible to switch between the drill down levels without actually drilling into just one year, or quarter? To elaborate, is it possible for a user to see a graph of these sales by quarter for 3 or 4 years? Right now I can only see the quarterly drill down if I select just one year, say 2008. But a user might want to see the quarterly trend over the last 6 years, or a monthly trend for the past 3 years.


                          Use a cyclic group instead of a drill down group.

                            • Input Box to control Line Graph
                              HeyZeusChris

                              Thanks for your help. I have it working now...

                              I went into Document properties -> Number, with "Fields" selected in my drop down I noticed that my Date field had Timestamp number format selected. I changed it to date. In addition, I changed the drop down to "Variables" and changed my StartDate and my EndDate to both Date number Formats instead of the default selected Mixed.

                              After doing this the original formula you provided seems to have worked.

                              You were correct about cyclic groups. It is confusing though because where I come from a cyclic group is: http://en.wikipedia.org/wiki/Cyclic_group

                              Again, Thank you!!! Thank you!! Thank you!!

                                • Input Box to control Line Graph

                                  Hi,

                                  I am also having the same requirement at my side.

                                  Could you kindly show me the script. I have to show the month wise trend wherein the succeeding month's total should be inclusive of previous month total. And my period starts from 2000 onwards.

                                  Thanks in advance.

                                  Gaurav

                            • Input Box to control Line Graph

                              Hi John,

                              I have to show trend month and year wise(both taken on x-axis) corresponding to total no. of "request_id" coming in for the access of different applications in our company.

                              Please see the sample data.

                              Date Request_id Application_id

                              01-01-2000 1 2

                              03-04-2001 2 3

                              08-09-2006 3 4

                              and so on........

                              We have data like this starting from 01-01-2000 till today's date.. We have many applications, and users have to sent send request_id to us to access those applications.

                              We want to show the cumulative value of no. of requests received so far month wise for example. Jan 2011 should have the closing balance of no. of requests received till end of 2010(the period starts from 01-01-2000). Feb 2011 should have the closing balance of Jan 2011, and Mar 2011 should have the closing balance of Feb 2011, and so on... Like this we have to show trend month wise starting from year 2000 till today's date.

                              Please advise...how to achieve this...

                              Thanks a lot in advance...

                              Gaurav Varshney