Alter the TOTAL Qualifier

[sstefancies]

I am working with the TOTAL qualifier, trying to get proper percentage of totals.

I'm running into a problem where TOTAL does not return a SUM:

Service Inv Amt =

Sum( {$<[Invoice-Type]={'SER'}>}   [invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10])

If left alone, the total in that column would be 128,140,264.67.  However, that does not accurately reflect the total of the column.  Changing the "Totals function" to "Sum" fixes it.  However, this does not fix the way the total qualifier works.  The "Total" column is:

  ( sum(total {$<[Invoice-Type]={'SER'} >}  

        [invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10]) )

How can I force the TOTAL column to show the actual SUM of my expression?

[sunny_talwar]

Can you try this:

Sum(TOTAL {$<[Invoice-Type]={'SER'}>}

RangeSum([invoice.Gross-amt], [invoice.Extra-Charge__1], [invoice.Extra-Charge__2], [invoice.Extra-Charge__3], [invoice.Extra-Charge__4], [invoice.Extra-Charge__5], [invoice.Extra-Charge__6], [invoice.Extra-Charge__7], [invoice.Extra-Charge__8], [invoice.Extra-Charge__9], [invoice.Extra-Charge__10]))

[swuehl]

Seems like you want to calculate a sum-of-rows instead of evaluating the expression in total context.

Try something like

=Sum( TOTAL

Aggr(

YOUREXPRESSION, YOURCHARTDIM1, YOURCHARTDIM2, YOUCHARTDIM3

))

or

=$(=Sum(

Aggr(

YOUREXPRESSION, YOURCHARTDIM1, YOURCHARTDIM2, YOUCHARTDIM3

))

)

[sstefancies]

The first suggestion didn't change the total.  I apologize - I failed to mention that the reason that the sum of "Service Inv Amt" is so much lower is because limitations have been placed on the dimensions to only show lines where the "Service Inv Amt" is larger than 1500. 

That being said, one of the things I tried was to put something in the set analysis of the "Total" statement to only include amounts that are greater than 1500.  This failed because set analysis can only measure field names (not calculated measures) 

Using the second suggestion and bringing in AGGR might be useful but I'll have to play around with it.  Both of the specific AGGR suggestions brought the number down from 128 mil to 91 mil... I'm working on where that number is coming from.

Thanks for the suggestions thus far!

[sunny_talwar]

What is your calculated dimension?

[sstefancies]

vInvAmt is a variable the is defined as:

[invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10]

The idea was to use it in set analysis:

sum(TOTAL {$<[Invoice-Type]={'SER'}  , vInvAmt={">1499"}  >}  

[invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10])

I realize that variables are supposed to be in proper format ( $(vVariable) ) but that doesn't work in set analysis.  Using the variable without the formatting yields the same number that it did without using the variable in the set statement. (128,140,264.67).

[swuehl]

You can only use fields from your data model left of the equal sign in a set analysis field modifier.

[sunny_talwar · Accepted solution]

How about this?

Sum(TOTAL {$<[Invoice-Type]={'SER'}, [SO No.] = {"=[invoice.Gross-amt]+[invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10]>1499"}>}

[invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10])

[sstefancies]

I think this is the one!  The total is off but only slightly.  I'm investigating, but believe that it's likely due to anomalous data.  This is really going to help me in the future as well.  I had no idea that you could analyze a calculation in set analysis using a field.  Like Stefan said, you can only use fields from the data model left of the equal sign.  This was impeding my progress. 

Thank you so much!! 

[sunny_talwar]

One thing you might want to check is that you want to check the Sum of all these fields over a SO No. or another unique field? This field should not have any null values for the 10 fields you are summing up. because any row where one of these values is null, the calculation for the row will fall out.

You can try this also and see if it fixes the small discrepancy you are still having:

Sum(TOTAL {$<[Invoice-Type]={'SER'}, [SO No.] = {"=RangeSum([invoice.Gross-amt], [invoice.Extra-Charge__1], [invoice.Extra-Charge__2], [invoice.Extra-Charge__3], [invoice.Extra-Charge__4], [invoice.Extra-Charge__5], [invoice.Extra-Charge__6], [invoice.Extra-Charge__7], [invoice.Extra-Charge__8], [invoice.Extra-Charge__9], [invoice.Extra-Charge__10])>1499"}>}

RangeSum([invoice.Gross-amt], [invoice.Extra-Charge__1], [invoice.Extra-Charge__2], [invoice.Extra-Charge__3], [invoice.Extra-Charge__4], [invoice.Extra-Charge__5], [invoice.Extra-Charge__6], [invoice.Extra-Charge__7], [invoice.Extra-Charge__8], [invoice.Extra-Charge__9], [invoice.Extra-Charge__10]))