total as sum of rows

[#1]

Hi to All,

I've a pivot table with an expression (% SellIn) which total I want get as the sum of rows in detail.

For example, I want get the total for  "% SellIn" in  week 01  to be 87,3% , not 100%.

Have you any suggestion?

Thanks in advance

stefano

[swuehl]

In a pivot table chart your total is evaluated as expression total. You will noticed that the Total Mode option is disabled in expression tab for a pivot. But there is a workaorund using advanced aggregation, please check the Help for sum of rows in pivot tables.

Basically you do something like:

sum(aggr( YOURXPRESSION , DIM1, DIM2, DIM3, ..))

Unfortunately, you are using a lot of calculated dimensions, which you can't just put in as aggr() dimensions. So I would suggest to pre-calculate your calculated dimensions in the load script, creating new fields, which seems to be possible having had a quick look to your dimensions. Then use your new fields as dimensions in your chart and in your aggr() function.

Hope this helps,

Stefan

[#3]

Hi Stefan,

I know what you written about aggr() function but even if I use NOT calculated dimensions, I'm not able to get the result I want in total row of "% SellIn" expression.

Thanks

stefano

[swuehl]

Hi Stefano,

could you share your sample without the calculated dimensions?

I also noticed that your % values in each line are the same for each week, is this what you expect?

[#5]

Hi Stefan,

I attached the .qvw without the calculated dimensions.

% SellIn must be equal for all weeks if SellOut is not null, otherwise % SellIn will be null.

For example % SellIn is different between week 01 and week 02. The Total for week 01 is 100 % and the total for week 02 is 87,3 %

Thanks

[swuehl · Accepted solution]

Try this as expression:

sum(

aggr(

if(   count(week)>0 and sum(sellOutQtaWhoPub)>0,

    sum({ < year = { $(#=(only(year)-1)) },cliFlagSellOut={1} > } TOTAL <cliPartitaIvaCodFiscale> sellInQta)

  /

   sum({ < year = { $(#=(only(year)-1)) },cliFlagSellOut={1} > } TOTAL sellInQta)

  ),

  cliPartitaIvaCodFiscale,cliRagioneSociale, week))

[#7]

Thank you very much swuehl, your answer is perfect!