topic Calendar chart in QlikView

Calendar chart

richardpayne — Tue, 16 Feb 2010 21:53:47 GMT

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.

Calendar chart

johnw — Tue, 16 Feb 2010 22:36:43 GMT

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.

Calendar chart

richardpayne — Wed, 17 Feb 2010 15:36:01 GMT

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.

Calendar chart

johnw — Wed, 17 Feb 2010 19:23:15 GMT

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?

Calendar chart

Wed, 17 Feb 2010 19:25:59 GMT


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!

Calendar chart

richardpayne — Wed, 17 Feb 2010 20:27:00 GMT

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?

Calendar chart

johnw — Wed, 17 Feb 2010 20:59:33 GMT

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

Calendar chart

Wed, 17 Feb 2010 21:05:29 GMT

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

Good Example John!

Calendar chart

johnw — Wed, 17 Feb 2010 21:10:25 GMT

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.

Calendar chart

richardpayne — Wed, 17 Feb 2010 21:47:07 GMT

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

Calendar chart

johnw — Wed, 17 Feb 2010 21:58:36 GMT

Glad to help. And in case you go with the little green graphs, I think it would be better to use this as the gauge's maximum than what I posted before:

max({1}aggr(sum({1}Sales),MonthYear,MonthWeek))

That way, you can see the trend in the weekly totals, not just in the daily totals. And when you select a particular month, the size of the green boxes won't suddenly change on you. They're still big enough to tell the difference in size on a daily basis, for the most part.

Calendar chart

Thu, 18 Feb 2010 15:08:48 GMT

Hi John,

Please could you post the code for your Week dimension. When I first looked at it I thought it held values 1-53, I now see It only holds values 1-6.

Have you generated this or is it manually entered?

Thank you,

Kind Regards,

Neil

Calendar chart

richardpayne — Tue, 06 Apr 2010 16:21:50 GMT

I'm struggling now to add the monthly totals. Is there a way to concatenate the sum of the sales to the MonthYear?

I can get the monthly totals by showing partial sums by week and day, but seeing the the sales over all Wednesdays, for instance, is useless information.

Calendar chart

Tue, 06 Apr 2010 16:39:10 GMT

Hi John,

Need you help please.

have a statistics box in which i am displaying total count of records.
I need to map this value to a variable using macro to do further
calculations. I used

Set SB = ActiveDocument.GetSheetObject("SB05")

When i am trying to use getfield to assign the value to a variable it is not accepting.

Please help.

Calendar chart

johnw — Mon, 12 Apr 2010 18:15:17 GMT

How about something like this?

set MyCount = activedocument.evaluate("count(MyField)")

Calendar chart

richardpayne — Fri, 28 May 2010 20:09:50 GMT

Our users want the visibility of the monthly total ($61M here), but the totals by day of the week are useless. Is there a way to retain the "Total" row, but remove all but the last sum?

So far this is the only way I've figured out to include the monthly total here in the chart.

Calendar chart

johnw — Fri, 28 May 2010 20:31:21 GMT

This seems to work for the posted example. I just checked what the dimensionality() and secondarydimensionality() were for the totals we wanted to exclude, and manually excluded those values in the formula itself.

if(secondarydimensionality(),day(Date)) & '
' & if(sum(Sales)
and not ( dimensionality()=1
and secondarydimensionality()=1)
,num(sum([Sales]),'$#,##0;($#,##0)'))

Calendar chart

richardpayne — Fri, 28 May 2010 21:33:25 GMT

Thanks! This pointed me in the right direction.

I'm actually using two expressions for day number and Sales, so I modified my Sales expression to consider dimensionality and column number.

Thanks again!