topic Re: Alter the TOTAL Qualifier in App Development

Alter the TOTAL Qualifier

sstefancies — Tue, 06 Dec 2016 16:33:16 GMT

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?

Re: Alter the TOTAL Qualifier

sunny_talwar — Tue, 06 Dec 2016 16:41:03 GMT

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]))

Re: Alter the TOTAL Qualifier

swuehl — Tue, 06 Dec 2016 17:04:18 GMT

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

))

=$(=Sum(

Aggr(

YOUREXPRESSION, YOURCHARTDIM1, YOURCHARTDIM2, YOUCHARTDIM3

))

)

Re: Alter the TOTAL Qualifier

sstefancies — Tue, 06 Dec 2016 17:15:55 GMT

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!

Re: Alter the TOTAL Qualifier

sunny_talwar — Tue, 06 Dec 2016 17:30:57 GMT

What is your calculated dimension?

Re: Alter the TOTAL Qualifier

sstefancies — Tue, 06 Dec 2016 17:47:09 GMT

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).

Re: Alter the TOTAL Qualifier

swuehl — Tue, 06 Dec 2016 18:03:38 GMT

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

Re: Alter the TOTAL Qualifier

sunny_talwar — Tue, 06 Dec 2016 18:07:35 GMT

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])

Re: Alter the TOTAL Qualifier

sstefancies — Tue, 06 Dec 2016 18:32:01 GMT

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

Re: Alter the TOTAL Qualifier

sunny_talwar — Tue, 06 Dec 2016 18:37:10 GMT

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"}>}

Re: Alter the TOTAL Qualifier

sstefancies — Tue, 06 Dec 2016 19:03:04 GMT

That fixed it! Changing to RangeSum and using 1499.99 gives me perfectly matching numbers every time. Thanks again!

Re: Alter the TOTAL Qualifier

sunny_talwar — Tue, 06 Dec 2016 19:04:13 GMT

Not a problem. I am glad we were able to help.

Best,

Sunny