Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
james_hastings
New Contributor III

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?

1 Solution

Accepted Solutions
MVP
MVP

Calendar Object

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

View solution in original post

13 Replies
Not applicable

Calendar Object

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

Not applicable

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

james_hastings
New Contributor III

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.



MVP
MVP

Calendar Object

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

View solution in original post

james_hastings
New Contributor III

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?



MVP
MVP

Calendar Object

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.

Highlighted
james_hastings
New Contributor III

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?

MVP
MVP

Calendar Object

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.

james_hastings
New Contributor III

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?