topic Re: Set Analysis with Aggr and formula in QlikView

Set Analysis with Aggr and formula

Mon, 07 Nov 2011 19:44:13 GMT

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.

Re: Set Analysis with Aggr and formula

Tue, 08 Nov 2011 01:50:17 GMT

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

Tue, 08 Nov 2011 14:09:41 GMT

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.

Re: Set Analysis with Aggr and formula

Tue, 08 Nov 2011 18:15:40 GMT

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

Tue, 08 Nov 2011 18:40:40 GMT

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

Tue, 08 Nov 2011 19:10:23 GMT

Data attached

Re: Set Analysis with Aggr and formula

Tue, 08 Nov 2011 19:23:47 GMT

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

Tue, 08 Nov 2011 19:27:30 GMT

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

Tue, 08 Nov 2011 19:34:45 GMT

Done.

Kiran.

Re: Set Analysis with Aggr and formula

Tue, 08 Nov 2011 20:56:50 GMT

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

Wed, 09 Nov 2011 05:07:24 GMT

{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

Wed, 09 Nov 2011 13:49:32 GMT

Thank You