13 Replies Latest reply: May 15, 2009 2:57 PM by Robert Henry RSS

    Calendar Object

      Background:
      Let's say that you looking at the total revenue made on May 11,2009. Naturally you want to compare this number to the revenue you made on May 11, 2008. Only to find that you made significantly less this year than last. You do some research and find that the drop is because May 11 last year, fell on a Sunday (your busiest day of the week) and May 11 this year fell on a Monday (your slowest day of the week.)

      More on Background:
      It won't just be a specific day as well. Some months have an extra Sunday when comparing previous years. So when comparing a Month-to-Date scenario, or a total month revenue, that extra Sunday will throw a bit of a skew in a progress curve.

      Issue:
      I need help adding two or more calendar objects (or something similar) next to my graph. That shows the current selected month, and each seperate calendar object shows the same month for previous years. That way I can have a solution to comparing the selected month for each selected year.

      Ideas on Solution:
      Is there a calendar object, which I can set the current month and year to the selected current month and years via a macro?
      I have though about using Images of each month and year, but this would require alot of images, time, and maintenance. If Images is the only way, How do you set the current image via a macro?

      Any input on a possible solution, even if it is not mentioned above, would be valued.

      Also, where is there a good guide on qlikview macro commands and syntax?

       

        • Calendar Object
          Community Administrator

          James,

          I'm hardly the expert that the other guys and gals are, but here's a QlikView that has some calendar object examples that might be a good starting point.

          http://community.qlik.com/media/p/63238.aspx

          • Calendar Object

            I maybe way off the mark, but how about creating a Calendar Object as Input Style from your date fields.

            Or you could use a variable instead of your date field ?

            Regards

            Eraaj

             

              • Calendar Object

                 

                You guys are close, but I need something like a paper calendar. The calendar on the calendar input is close, but it has the limitation of not being open all the time. I need something that is more or less an image, something that doesnt interfere with the displayed data at all.

                Lets say I have three list boxes:
                Day = 11
                Month = May
                Year = 2009
                Then I would have 1 calendar "Image/object" displayed next to the graph.

                If:
                Day = 11
                Month = May
                Year = 2009 & 2008
                Then I would have 2 calendar "Image/objects" displayed next to the graph. One would be of May 2009, the other would be May 2008.

                It is a simple calendar image that is dynamically set according to the selected date. If this ends up being an Image, then I need to crop 60 images (12 months for the past 5 years), and keep doing this per month (high maintenance), which is bad news. If it has to be images, I also need a macro that can set the image path to a certain file. As you can see, an object that can be set dynamically is what I really need.



                  • Calendar Object
                    John Witherspoon

                    I don't think this is a complete solution, but here are generated calendars for the selected month of the selected years. They assume that the week starts on Monday, the QlikView default. If you want a week that starts on Sunday, I can do that instead (getting the week number right in that case is actually rather complex, but I've got the formula somewhere).

                      • Calendar Object

                        Excellent!

                        To make it more dynamic, I am replacing the Dimensions with:
                        For current year:
                        =if(year(date)=(Year(Today())),week)
                        =if(year(date)=(Year(Today())),weekday)

                        For previous Year:
                        =if(year(date)=(Year(Today())-1),week)
                        =if(year(date)=(Year(Today())-1),weekday)

                        That way the one on top will always be the most current year, the one below will be prior year, etc.

                         

                        The Monday to Sunday format is fine, but if you have the formula for a Sunday to Saturday format, that would be nice to have.

                        My only gripe is the first two columns. Is there a way to "hide" the week column and the blank column, so all that is left are the days and weekdays?



                          • Calendar Object
                            John Witherspoon

                            Here's a Sunday through Saturday calendar. Hopefully I got it right. I couldn't figure out how to hide the week and blank columns, but I did deemphasize them, turning them into a consistent grey border. You could also shift them a little off the screen or overlay them with other sheet objects or a background color text box to get rid of the grey completely.

                              • Calendar Object

                                I looked through the chart properties, and can't figure out what you changed to make the columns gray. What is your secret?

                                  • Calendar Object
                                    John Witherspoon

                                    I used some hidden properties. I think you first have to go into settings, user preferences, design, and put a check mark next to "Always Show Design Menu Items". Then when you right click on a cell, one of the options in the list that comes up will be "Custom Format Cell". Click on it, and it will bring up an extra list of properties that you can apply to that cell, such as background color, borders, and text color and size. These will override your Style properties, and the Style will be set to custom. I've occasionally noticed bugs, where sometimes it just doesn't work, and sometimes it sets the entire cell to black, and is unrecoverable. I recommend saving before poking at it.

                                      • Calendar Object

                                        Hmm, I can't seem to get rid of the bold "L" in between the first column, and the blank column. Also there doesn't seem to be anyway to change or add a horizontal grid line in between the numbers.

                                        I was able to get rid of the 'L' by changing the style to type 'clean', then messing with the cells format, but then the horizontal gridline disappeared.

                                        What did you do to fix both? or am I just missing something?

                                          • Calendar Object
                                            John Witherspoon

                                            You probably just need to keep experimenting, but we can probably use the first example I posted as a starting point and example. Right click on the week heading cell, and select custom format cell. Set the background color to 220,220,220, which is the same gray as the grid lines. Do the same for the week data cell. You should then have the gray bar like I had, just with a heading and values in it. Easy enough to remove those, as indicated in the second example I posted, leaving you with only gray bars.