7 Replies Latest reply on May 15, 2018 1:57 PM by Prakash Madhavan

Subtract instead of Sum as Grand Total

Hi

I feel this is something ridiculously simple, but currently I have:

Type
Count
Gross Inactive100
Gross Active150
Grand Total250

And what I am trying to achieve is:

Type
Count
Gross Inactive100
Gross Active150
Grand Total50

From what I can tell, table calculations are only useful for replacing the actual end result, not the Grand Total calculation.

Am I missing something obvious?  It may be important to note that the split by type is achieved with a bin calculation.

Thanks!

• 1. Re: Subtract instead of Sum as Grand Total

Well I haven't tried it with bins (I wasn't quite sure what you meant), but I've attached a workbook that shows how you can display what you want just with raw numbers.

The trick I used was to arrange for the inactive numbers to be negative, but just suppress the minus sign by defining a custom format mask of:

#;#

That just says display both +ve and -ve numbers without a sign.

2 of 2 people found this helpful
• 2. Re: Subtract instead of Sum as Grand Total

Richard,

As always, thanks for your help!

Actually, showing what I have as a negative would be even better (I wouldn't need to hide the the negative sign, I just wasnt sure how I could do it).

However, I think due to how I am using the bins, this will not work (but is on the right track, I can feel it!)

I'll try and explain...if you dont follow me let me know and i'll construct a demonstration package workbook.

Basically, to identify the Inactive / Active, I have a bin calculation on a calculated column that goes:

IF ([AUS_VOLUME_R24]+[AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]) >0

AND ([AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]+[AUS_VOLUME_R12]) <=0

THEN 1

ELSEIF ([AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]+[AUS_VOLUME_R12]) >0

AND ([AUS_VOLUME_R24]+[AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]) <=0

THEN 2

ELSE 0

END

Each column is a previous month's total transactions (the [AUS_VOLUME] etc.)

1 will equal a customer that was previously inactive, but is now inactive

2 will equal a customer that was previously active, but is now inactive

0 will equal all others that did not move

Then I am using bins to show the 0, 1, 2 and just renaming the label, excluding 0 (as I am just trying to see my "net movements") and then doing counts in my data based on customer ID.

This has been the most effective and the fastest way I have found so far to use my large calculated fields when it comes to splitting by history.

Soooo....am I screwed?

• 3. Re: Subtract instead of Sum as Grand Total

You know what, I have only just figured out how to show the table calculation as well as the actual values on the table calculation.

I think I can get around it with that - ill see what I can work out.  It's not a major if the totals are on the right.

EDIT:

Yeah, I found a rather "rough" way to get around it with some smart labeling.

Not the best, but it does the job!

• 4. Re: Subtract instead of Sum as Grand Total

I only glanced at it - but if you use -1 for "has become inactive", 0 for "no movement" and 1 for "has become active" doesn't it just do exactly what you want?

• 5. Re: Subtract instead of Sum as Grand Total

Yup it would if I was summing the outcome - but I'm using bins instead, and counts on the bins.

It's all good, I worked around it with some crazy formatting

• 6. Re: Subtract instead of Sum as Grand Total

I think you could just have two calculated fields - one that gives the active/inactive classification as you have at present) and one that gives the value 1 or -1 and then just use sum() instead of count.

But if you're happy with your crazy formatting that's fine...