Calendar chart

[richardpayne]

Has there been any advancements in the creation of calendar charts?

I am attempting to show a calendar, emulating a paper calendar, which shows the day number and sales total within each cell. We are new to Qlikview, and this was discussed during implementation, though no solution was found.

I've see the pseudo-calendars discussed in the forums, with week of month and day used to synthesize the calendar view, but this technique does not fulfill our users' requirements.

Currently, I'm attempting to show date and values with a concatenated expression, but that leads to unintended consequences with totals.

[johnw]

I'm not aware of any calendar improvements that would directly support this sort of thing. So far as I know, the psuedo-calender is about as close as you can get.

I'm not sure what approach you're taking to concatenate dates and values, but if you're trying to, say, sum(Sales) by date, and have a total across all dates, perhaps this would work as your expression:

dual(if(only(Date),Date,'Total') & ' ' & sum(Sales), sum(Sales))

Dual() says the result has both a text value and a numeric value. So we'll display text that includes a date or 'Total' plus the sum of the sales. But the underlying numeric value is just the sum of the sales.

[richardpayne]

Thank you for your quick reply.

I am attempting to display a date and the sum of sales within a cell. So my expression looks something like:

=day(Date) & ' ' & num(sum([Sales]),'$#,##0;($#,##0)')

This will give me '4 $1000' in a cell where day is MM/04/YYYY and the daily sales total is $1000. If there is one cell within the week that is populated, the weekly partial sum for that row will be '4 $1000'. If there were two cells in that row, '4 $1000' and '5 $500', then the total says '$1500', leaving off the day number.

I've attempted to attach a sample QVD, but the upload feature here is not working for me in either IE or Firefox at the moment.

[johnw]

So... are you saying that that expression gives those results? That expression does NOT give those results? Are those the results you want? Did you want some other results? If so what other results? What sort of help are you looking for?

[#5]

richardpayne wrote:
I've attempted to attach a sample QVD, but the upload feature here is not working for me in either IE or Firefox at the moment. <div></div>

This feature should work now...sorry for the inconvenience!

[richardpayne]

I am concatenating the day of the month and the sum of sales for that day in each calendar cell intentionally. Having one or the other would not meet the users' requirements.

I've attached an example of the calendar format I am using. You can see the total for week 1 and week 6 of Jan 2010 showing the day of the month. I would like to change this to show only the sum of the dollar amounts from the week.

I think this example clearly demonstrates what I am attempting to do. Is there a better way of displaying this information in this type of format?

[johnw · Accepted solution]

OK, now I think I see what the problem is, and what you want. Here are three different approaches. They all remove the day from the total column, and then just display the results in a few different ways. I think the third one is the most easily read, but it also uses the most space.

(EDIT: You can take the third calendar even further. Add a third expression with a value of space between the day and the amount. Custom format the cell to remove the lines before and after the expression. Now it looks even MORE like a calendar, and makes it even easier to read. However, it also then takes even MORE space, which I find is almost always at a premium.)

[#8]

I would agree with John the third one is the best.

Good Example John!

[johnw]

Extended on the third one. Added a little box of light green that gives you a VERY quick visual recognition of which days had big numbers and which had small numbers. Might or might not be worth the space, but far easier to comprehend at a glance than a large table of numbers. Might be able to do even better in version 9, which I think gives you more options for graphs inside of a cell like this.

[richardpayne]

Thanks for all your help and patience. This is right on the money.