4 Replies Latest reply: Jan 24, 2012 5:27 PM by alphiealphie RSS

    Fixed Year Gauge Charts

      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?

        • Re: Fixed Year Gauge Charts

          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

            • Re: Fixed Year Gauge Charts

              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.