Pivot Table - Customise 'Partial Sum' total to Subtract instead of Add

[DSTaylor]

Hi,

Is there a way to use the Partial SUM in a PIVOT Table to perform a subtraction instead of addition?

I have only 2 values in the dimension:

- Actual

-Planned

I want the Partial Sum Total to show Actual-Planned instead of Actual+Planned.

My expression is:

=num(sum(
if(WorkDate<=date(today()),
if(ProjectFlag='Y',
if([Dept]='Body' or [Dept]='Tyre',
HOURS)))),'######')

Thanks!

[jonathandienst]

Use Dimensionality() to control the expression. Something like this:

If(Dimensionality() = 1,

	Sum({<WorkDate = {"<=$(=Date(Today())"}, ProjectFlag = {Y}, 
		Dept = {'Body', 'Tyre'), MYFIELD = {Actual}>} HOURS)
	-
	Sum({<WorkDate = {"<=$(=Date(Today())"}, ProjectFlag = {Y}, 
		Dept = {'Body', 'Tyre'), MYFIELD = {Planned}>} HOURS)
	,
	
	Sum({<WorkDate = {"<=$(=Date(Today())"}, ProjectFlag = {Y}, 
		Dept = {'Body', 'Tyre'), MYFIELD = {Actual}>} HOURS)
	+
	Sum({<WorkDate = {"<=$(=Date(Today())"}, ProjectFlag = {Y}, 
		Dept = {'Body', 'Tyre'), MYFIELD = {Planned}>} HOURS)
)

You will need to adapt this by changing the field name and checking the structure of the pivot against my assumption of Dimensionality of 1.

[Brett_Bleess]

Please be sure to return to your thread to close things out, I am assuming Jonty's response got you what you needed, but I  would like you to confirm by using the Accept as Solution button on his post if that did work for you, which will close the thread, give him credit for the help and let other Members know it worked for your use case.  If you did something different, please consider posting that and then use the button to mark that post as the solution to close the thread.  If you are still working on things, please leave an update.  

The only other method of which to do things would be to have one of the numbers be a negative to get the subtraction you want, and I would assume you might have to set that during the load of the data etc., but could maybe do that to an alias of the field that you use in the pivot table just for this calculation, not sure if it will work well or not, but wanted to toss it out there.

You can always search within the Design Blog area for things that may give you ideas too:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett