
Re: Set Analysis with Aggr and formula
Kiran Rokkam Nov 7, 2011 8:50 PM (in response to Patrick Callocchia)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.

Re: Set Analysis with Aggr and formula
Patrick Callocchia Nov 8, 2011 9:09 AM (in response to Kiran Rokkam )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.

QLK.xls 306.0 K

Re: Set Analysis with Aggr and formula
Kiran Rokkam Nov 8, 2011 1:15 PM (in response to Patrick Callocchia)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.

Re: Set Analysis with Aggr and formula
Patrick Callocchia Nov 8, 2011 1:40 PM (in response to Kiran Rokkam )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.

Re: Set Analysis with Aggr and formula
Patrick Callocchia Nov 8, 2011 2:10 PM (in response to Patrick Callocchia)Data attached

Re: Set Analysis with Aggr and formula
Kiran Rokkam Nov 8, 2011 2:23 PM (in response to Patrick Callocchia)Please find the updated qvw with a text box with brown background.
It seems to work for me.
With Selections:
Without Selections:
Kiran.

Re: Set Analysis with Aggr and formula
Patrick Callocchia Nov 8, 2011 2:27 PM (in response to Kiran Rokkam )If I selelct a tenure it changes the value. Shouldn't set analysis stay the same no matter what the current selections are?

Re: Set Analysis with Aggr and formula
Kiran Rokkam Nov 8, 2011 2:34 PM (in response to Patrick Callocchia)
Re: Set Analysis with Aggr and formula
Patrick Callocchia Nov 8, 2011 3:56 PM (in response to Kiran Rokkam )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.

Re: Set Analysis with Aggr and formula
Kiran Rokkam Nov 9, 2011 12:07 AM (in response to Patrick Callocchia){1} ignores all selections made for that calculation. For more information read set analysis in qv help. Some parathesis error. Fixed in the attachment.
Kiran.

Re: Set Analysis with Aggr and formula
Patrick Callocchia Nov 9, 2011 8:49 AM (in response to Kiran Rokkam )Thank You









