3 Replies Latest reply: Mar 8, 2012 10:36 PM by nlombardi RSS

    Chart aggregation with Dimension missing Data

      Hi all

      I am trying to show a chart on each Salesperson's dashboard that will show the entire team's sales so they can compare how they are doing against the entire team.

       

      Data:

      IDSalespersonNameTeamItemRevenueYearMonth
      001NickNorthPizza1520121
      001NickNorthHotDog1020121
      002KatieNorthPizza1220121
      002KatieNorthPizza3020122
      003HenryNorthPizza720121
      003HenryNorthHotDog1020122
      003HenryNorthHotDog1620121
      004RyanWestPizza4020121
      004RyanWestHotDog10120121
      004RyanWestHotDog4520122

       

       

      on the Pages for Nick and Henry, the graph displays correctly: 64 pizza vs 36 HotDog (Nick/Katie/Henry are all on the "North" team, so Ryan's sales are ignored)

       

      correct.jpg

       

      however the graph for Katie is incorrect, I am presuming because she only sold Pizza, but i want her graph to look like the above since she is still on the North team regardless of the fact that she sold 0 Hot Dog Individually.

       

      bad.jpg

       

      My chart is basaed on the "Item" dimension, expression is

       

      =aggr(nodistinct sum({<Id=,SalespersonName=,Year={'2012'},Salesgroup=>}Revenue),[Item],[Salesgroup])

       

      i think i am missing an if statement or something

       

      thanks in advance!

        • Chart aggregation with Dimension missing Data
          Stefan Wühl

          I haven't fully understood how your chart is build. You are using only one dimension (Item) and one expression (see above), but how do you get the stacked bar chart and how do you relate the chart to the salesperson on which dashboard the chart is located?

           

          In general, I would assume that you don't need the aggr() function, a sum with TOTAL qualifier  and field list should do, maybe something like

           

          sum(TOTAL<Item, Salesgroup> Revenue)

           

          (this probably won't do, you might need to add the set expression to limit the data like you need etc, and I am not sure about the correct field list to TOTAL, since I don't understand your setting).

           

          Regards,

          Stefan

          • Chart aggregation with Dimension missing Data

            I think i may have solved the issue..let me know if you see any problems with this formula, it seems to be working...

             

            i added two expresions:

             

            =num(aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam={'HotDog'},Salesgroup>}Revenue), Salesgroup)
            /
            aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam,Salesgroup>}Revenue), Salesgroup),'#,##0%')


            =num(aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam={'Pizza'},Salesgroup>}Revenue), Salesgroup)
            /
            aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam,Salesgroup>}Revenue), Salesgroup),'#,##0%')