Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Add total only formula to Pivot? (No dimension level totals)

I am creating a balance sheet using a pivot table. In the table the formula for balance is this:

if(fiscalperiod=0,0,creditamt + debitamt) +openbalance))

This works great. However I need to add another formula that is only calculated for the total of the pivot table for thew L&E (Not needed per dimension)

if(left(text(segvalue1),1)<>'1',if(fiscalperiod=0,0,creditamt + debitamt) +openbalance)

At the moment I have this formula in a seperate list box, b ut if they want to export the information to Excel, this info would not be included.

Is there any way to add it to the pivot table, but have it only appear at the bottom as a total?

8 Replies
swuehl
MVP
MVP

I think you could try this:

In presentation tab, set show partial sums only for the top dimension, and deselect for all other dimensions.

Then use as expression something like:

= if(rowno()=0,TOTALEXPRESSION, EXPRESSION)

where TOTALEXPRESSION is the one you want to use at the total level, and expression the one per dimension.

(ensure that both work as expected at the given location, for example take a look at the help regarding sum of rows with pivot tables).

Regards,

Stefan

Not applicable
Author

show partial sums is grayed out on my chart.

I got it to work by adding a dimension 'Total', then told the expression that it can not equal any of the values of the next dimension, which suppresses it for all levels except the total.

swuehl
MVP
MVP

That's sound interesting. But I don't get what you mean with telling the expression that it can not equal....

Could you post your expressions for the total dimension/expression or a sample app here?

Regards,

Stefan

Not applicable
Author

I added Dimension 1 as ='Total'

Dimension 2= Topincome ( values only of General and Administrative,Sales, Cost of Goods Sold, and Selling)

expression:

=if(Topincome<>'General and Administrative'

and Topincome<>'Sales'

and Topincome<>'Cost of Goods Sold'

and Topincome<>'Selling',$(Sales)-$(COGS)-$(NoInterest))

Since the only place where topincome='' or is null is on the total level of the dimension total, this is the only place the value shows up.

This is really only usable since I only have 4 values on the next dimension. If it were more, then it would not make since to use this solution.

untitled.JPG

swuehl
MVP
MVP

Ok, getting closer.

But then you have a dimension 'Total' / additional column in your chart, don't you?

And you are using a partial sum to show the total line, right?

Thanks for sharing,

Stefan

P.S: just saw your image, forget question 1)...

Not applicable
Author

I just have the chart set to indent mode, which adds the total columns to each dimension when they are expanded.

swuehl
MVP
MVP

Got it (and just learned about the indent mode)!

I think indent mode is also why the partial sums are grayed out.

So in the future, if the number of your dimension values increase or you want to get rid of the extra column,

I think you might wanna try again the above expression using rowno().

(This works also with indent mode, if you set partial sums with indent mode deselected, then switch to partial sums).

I've attache a sample of what I had in mind.

Anyway, thanks for sharing your solution, have a nice evening / day,

Stefan

Not applicable
Author

Hi,

I just read, understood and implemented what u hv shared above.

This is extraordinary feature.

Thanks a lot for sharing.

Keep it up.

Best Regards,

Happy Thoughts