Set Analysis with Aggr and formula

[#1]

I am starting with this Set Analysis using Aggr

sum({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61"}>}active_pct),tenure)))

This works fine. Now I am trying to replace active_pct with the formula

avg(active_pct) - (2 * Stdev(active_pct)

I have tried the followiong that do not work:

sum({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61"}>} (avg(activ_pct) - (2*Stdeve(active_pct)))),tenure)))

Sum({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61">}active_pct),tenure))) - (2 * Stdev({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61">}active_pct),tenure))) - This one is subtracting at too high of a level, I need the subtraction before the aggregation. 

I have tried to create a variable for the formula so I can just plug the variabe into the set analysis, but I can't get the variable to work, the variable always totals instead of giving a value for each individual tenure value. 

I tried to break this down to ensure the individual pieces work:

Using avg(active_pct) - (2 * Stdev(active_pct) if I total by Sum of Rows I get the correct number if I select tenure value of 1 - 60

Any help would be appreciated. 

[#2]

Hi,

Your expression: sum({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61"}>}active_pct),tenure))) and sum( aggr(avg({<tenure = {"<61"}>} active_pct),tenure)) are same. No need to have same set twice.

Now I am trying to replace active_pct with the formula

since avg is a group function having it instead of active_pct doesnt make sense to me. I think what you are looking for is something like a group by clause in SQL. For this you need to try TOTAL keyword.

If you can give an excel with the data and expected result (with excel formula) we can help you breaking it down.

Kiran.

[#3]

If I remove either set in the formula it does not work.  I thought this was strange as well, but it only works with both sets.  I saw something in another post about using it twice when using aggr.

I have attached a file which has two tables, one is in excel with the number we are trying to get (35.9198), the other is a table from QLK.  The avg(active_pct) - (2 * stdev(active_pct)) column has the total mode set to sum of rows.  This total (35.9198) is what we are looking for.  The current selections are set to tenure < 61, which corresponds to what I am trying to use set analysis for.

Thank You.

[#4]

Since the data is in preaggregated form I cannot confirm the result but the following expression should do the job.

=sum(Aggr(Avg({<tenure={"<62"}>} active_pct)-2*Stdev({<tenure={"<62"}>} active_pct),tenure))

Can you try. If not worked out please share the actual data and I shall work it out.

Kiran.

[#5]

Thank You for your replies.  That formula will work only if I choose the current selection to be tenure = <61.  So the set is not working correctly.  I will post the data shortly.

[#6]

Data attached

[#7]

Please find the updated qvw with a text box with brown background.

It seems to work for me.

With Selections:

Without Selections:

Kiran.

[#8]

If I selelct a tenure it changes the value.  Shouldn't set analysis stay the same no matter what the current selections are?

[#9 · Accepted solution]

Done.

Kiran.

[#10]

Great, Thank you, that works.  What does the {1} at the beginning do?

I am trying to add (* avg(pct_bal) onto the equation to make it (avg(act_pct) - 2 *(Stdev(act_pct))) * avg(pct_bal)

aggregated sum with set analysis

I am trying this:

=sum({1} Aggr(Avg({<tenure={"<61"}>} active_pct) - 2 * Stdev({<tenure={"<61"}>} active_pct) * (Avg({<tenure={"<61"}>} pct_bal)), tenure))

but its not working, any ideas?  I attached the data for reference.