topic Re: If works, set analysis doesn't? in QlikView

If works, set analysis doesn't?

Anonymous — Sat, 24 Oct 2015 20:14:06 GMT

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.

Re: If works, set analysis doesn't?

MK_QSL — Sat, 24 Oct 2015 21:09:21 GMT

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

Re: If works, set analysis doesn't?

Anonymous — Sun, 25 Oct 2015 07:30:30 GMT

I would suggest you to try like this;

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

Regards,

Imran K

Re: If works, set analysis doesn't?

Anonymous — Sun, 25 Oct 2015 08:54:47 GMT

Hi Dan,

May be:

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

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

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

Re: If works, set analysis doesn't?

Sun, 25 Oct 2015 10:22:38 GMT

Hi Dan,

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

are they expressions label, or a filed name.

Re: If works, set analysis doesn't?

Anonymous — Sun, 25 Oct 2015 14:14:18 GMT

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.

Re: If works, set analysis doesn't?

Anonymous — Sun, 25 Oct 2015 15:23:11 GMT

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.

Re: If works, set analysis doesn't?

swuehl — Sun, 25 Oct 2015 15:32:05 GMT

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.

Re: If works, set analysis doesn't?

Anonymous — Sun, 25 Oct 2015 15:37:50 GMT

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.

Re: If works, set analysis doesn't?

swuehl — Sun, 25 Oct 2015 15:43:37 GMT

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.

Re: If works, set analysis doesn't?

Anonymous — Sun, 25 Oct 2015 16:24:21 GMT

But it appears that it works fine when it's broken up into two parts.

([MRR] * Term_Flag) AS [MRR Comm TCV] - This is returning positive values (as expected)

([Churn MRR] * Term_Flag) AS [Churn Comm TCV], - This is returning negative values (as expected)

However, it seems no matter what formula I use to try and combine the two it either returns only negative or only positive.

Re: If works, set analysis doesn't?

swuehl — Sun, 25 Oct 2015 16:31:26 GMT

Hard to tell what the issue might be.

Could you give any examples for the input records and what you get as result of your calculations?

If one part is always much larger in absolute value than the other, I wouldn't be surprised to see only positive or negative values.

Re: If works, set analysis doesn't?

Anonymous — Sun, 25 Oct 2015 17:28:09 GMT

Attached an excel doc of the data dumped from the app.