Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

total as sum of rows

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

View solution in original post

6 Replies
Highlighted
MVP
MVP

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

Highlighted
Not applicable

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

Highlighted
MVP
MVP

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?

Highlighted
Not applicable

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

Highlighted
MVP
MVP

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

View solution in original post

Highlighted
Not applicable

Thank you very much swuehl, your answer is perfect!