topic Multiple Totals on Dataset in QlikView

Multiple Totals on Dataset

Anonymous — Fri, 27 Sep 2013 15:56:52 GMT

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

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?

Re: Multiple Totals on Dataset

spsrk_84 — Fri, 27 Sep 2013 16:49:52 GMT

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

Re: Multiple Totals on Dataset

Anonymous — Fri, 27 Sep 2013 17:18:17 GMT

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)

Re: Multiple Totals on Dataset

Anonymous — Tue, 01 Oct 2013 15:06:15 GMT

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

Re: Multiple Totals on Dataset

Anonymous — Tue, 01 Oct 2013 15:35:12 GMT

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),'##.##%')

)

Re: Re: Multiple Totals on Dataset

Anonymous — Tue, 01 Oct 2013 15:52:03 GMT

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

Re: Re: Multiple Totals on Dataset

Anonymous — Tue, 01 Oct 2013 17:33:04 GMT

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?

Re: Re: Multiple Totals on Dataset

Anonymous — Tue, 01 Oct 2013 18:01:20 GMT

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.