Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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

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!

2 Replies

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.