If works, set analysis doesn't?

[Anonymous]

Hi All,

I was wondering if anyone might be able to shed a little light onto something I don't understand.  I have a fairly simple formula:

if([Rate Type] = 'DCS', [Comm TCV] * [Rep Comm Rate %])

This works in my straight table.  However this does not:

=SUM({$<[Rate Type] = {'DCS'}>}[Comm TCV] * [Rep Comm Rate %])

It only returns zeros.  I was just curious if there was some reason why it may not work.

[MK_QSL]

Not sure but you can try below

=SUM({$<[Rate Type] = {'DCS'}>}([Comm TCV] * [Rep Comm Rate %]))

Which is equal to

SUM(if([Rate Type] = 'DCS', [Comm TCV] * [Rep Comm Rate %]))

or provide your sample data file or application..

[Anonymous]

I would suggest you to try like this;

=SUM({$<[Rate Type] = {'DCS'}>}[Comm TCV])*[Rep Comm Rate %]

Regards,

Imran K

[Anonymous]

Hi Dan,

May be:

=SUM({<[Rate Type] = {'DCS'}>}[Comm TCV] * [Rep Comm Rate %])

or

=SUM({<[Rate Type] = {'DCS'}>}([Comm TCV] * [Rep Comm Rate %]))

or

=SUM(if([Rate Type] = 'DCS', [Comm TCV] * [Rep Comm Rate %]))

[#5]

Hi Dan,

what are [Comm TCV] and  [Rep Comm Rate %] and [Rate Type] ,

are they  expressions label, or a filed name.

[Anonymous]

There are some negative values in the [Comm TCV] field.  Doesn't seem to matter which method I use to write the Set Analysis, it ends up multiplying only the negative values. 

[Anonymous · Accepted solution]

Edited reply:

Scratch that.  I see that I have [Comm TCV] both in the script, as well as an expression label.  So the problem is with the script and the math functions that are there.

.

[swuehl]

You are not giving much information about the context of your expression and your data model. For example, you are using this expression in a straight table chart, but with what dimensions used?

In general, consider using aggregation functions (talking about your first expression):

Use Aggregation Functions!

I can't really tell why your second expression returns only zero (or negative values also?), so any more details about your data, model and expression context probably will help.

[Anonymous]

I understand.  I couldn't really upload the file this time as it relates to internal employee compensation.  I believe that I have found the issue as part of the script.  I have to find a way to add two multiplied values. The following was only calculating the second part.

Alt([MRR] * Term_Flag) + Alt([Churn MRR] * Term_Flag)   AS [Comm TCV]

So the Alt([MRR] * Term_Flag)  is actually being excluded.

[swuehl]

Which means that [MRR] * Term_Flag doesn't return a numeric value for these records then.

Both fields numeric and within the input data record set?

Note: IMHO, Alt() with just a single argument (e.g. no default additional numeric to fall back to or an error value) is not making much more sense then just the single argument alone.