Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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

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

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

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.

MVP
MVP

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

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

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

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

MVP
MVP

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

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

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

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

MVP
MVP

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

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

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

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