Multiple Totals on Dataset

[Anonymous]

I have a simple Pivot Table Chart with the Dimension as Year, Month and a single Expression of SUM(Monthly Return). I am viewing the pivot with the Year as the rows and the Month as the column and the monthly return as the data item.

I want to see View MULTIPLE total on the dimension YEAR. So for example: I want to know the Mean and StdDev of returns for each month.

However I can only seem to able to set ONE total either Mean(Average) or the StdDev, but not both.

Expression for the TOTAL:

1. Expression for STDeV

(Dimensionality()=0 ,Stdev(FS_Fund_1M_Rtn), SUM(FS_Fund_1M_Rtn))

OR

2. expression for Avg

(Dimensionality()=0 ,AVG(FS_Fund_1M_Rtn), SUM(FS_Fund_1M_Rtn))

Is it possible to construct a pivot or a table chart with MULTIPLE TOTALS?

[spsrk_84]

Hi manish,

You need to write multiple expression as multiple totals is not possible.Other wise write two expressions one is for Mean & another for StdDev  and group them.so that u can select any one at a time.

if this is not what you looking for.. then please attach the sample format.

Thanks,

Shiva

[Anonymous]

It is possible with conditional expression, see attached

Edit:  This is just an illustration.  As for calculations, depending on what is needed (avg and sd dev on month level vs. record level), you maybe need this instead of Amount in the aggregations:
aggr(sum(Amount),Month,Year)

[Anonymous]

Michael,

Thank you VERY Much. It's not pretty but it works!!!

I'd prefer to have it as a avg/stddev of the Months rather than the year. But that's just changing the 'Show Partial Sums' at the Year and not the Month.

But more importantly I wanted to see the StdDev and Avg as two separate rows, rather than a single rows with two values. If you know how I can achieve this then it would be perfect.

But it seems to work.

Many thanks

[Anonymous]

Is there anyway of applying the Visual Cues on Totals that is constructed from conditional expression below?

Such that if the Avg or Stddev>0 then it's 'green' else 'red'?

(Dimensionality()=1 , SUM(FS_Fund_1M_Rtn),

'Mean  : ' & num(Avg(FS_Fund_1M_Rtn), '##.##%') & chr(10) &  'StDev : ' & num(Stdev(FS_Fund_1M_Rtn),'##.##%')

)

[Anonymous]

Manish,

Try attribute expression for the background color (or for text color), probably this:

if((Dimensionality()<>1
if(Avg(FS_Fund_1M_Rtn)>0 or Stdev(FS_Fund_1M_Rtn)>0,
  rgb(100,255,100),
  rgb(255,100,100))
)

See my example attached.  It is different but illustrates the aproach.

Regards,

Michael

[Anonymous]

Thanks Michael

That works for the entire text in the cell. However the only thing I would say is that I wanted the avg number to be green/red depending upon on whether it's positive or negative. But the Stdev I always want it black.

Any idea how I can split the colour of text in the one cell?

[Anonymous]

No such luxury, one cell - one color...  Don't have any idea at this time if its possible at all to split it into two cells.