Fixed Year Gauge Charts

[#1]

I have a table:

Name
Sales (from 2010-2011)
Date

I want to have a sheet, with all names down the left side. I want 2 Gauge charts, one for 2010 and one for 2011 which will show total sales for the selected Name.

I know I can use a listbox with Year(Date) but I would rather not in case my users forget to click it.

What expression should I use in the Gauge chart? Thanks.

As an extra nicity, I would like it so that the charts do not appear until a Name is selected. Is this possible?

[#2]

Hi Alphie

This is possible - you need to create 2 gauge charts and use set analysis in each one to restrict it to the year. Probably the easiest thing to do would be to create a new column called Year, based on your date, or your set analysis will get very complicated. you can't use Year(Date) as a dimension in set analysis so it would have to be specified as a range:

=sum({<Year={"2010")>} Sales)

=sum({<Date={'>=01/01/2010<01/01/2011'}>} Sales)

Alternately you could use an if statement, but this is very slow for lots of records and generally not used:

=sum(if Year(Date) = '2010', Sales)

In the layout tab in the chart properties, you can set the condition for the chart to appear by clicking on the option button "conditional" and entering a formula. In this case, you want something like getselectedcount(Name)= 1 or getselectedcount(Name) >0 which would show the gauge charts whenever any selection is made in Name.

Does this work for you?

Regards,

Erica

[#3]

The conditional appearance thing is perfect, thanks

As for the other issue, Im afraid this isnt working. The IF version also gives me zero. Im wondering if Im missing something basic. I cant upload a version as the data is sensitive

I added this as an expression:

=sum({<Year={"2010")>} Sales)

Gauge charts have no dimension right? So Ive added this as an expression but the value is always zero. I also added

Year(Date) as SalesYear

in the script to give me a Year value but the SUM always ends up as zero. Puzzled.

[#4 · Accepted solution]

Hi Alphie.

That's true, gauge charts have no dimension. You  can however add one if you are doing trellis charts. I don't think a calculated dimension would work as a filter tho.

I'm not sure why

=sum({<SalesYear={"2010")>} Sales)

Wouldn't work in your example. I've attached a test file that I work with, hope this helps you in some way....

Erica

[#5]

The example worked!

Thank you so much I can see now that its rather basic Set manipulation but its hard to get a feel for that without a good example.