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]

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.

[#3]

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

[richardpayne]

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.

[#5]

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.

[johnw]

How about something like this?

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

[richardpayne]

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.

[johnw]

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

[richardpayne]

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!