calculation context help

[Former Member]

Hi,

I have a simple report that consists of Account ID, Age Group, # Reg Group, and Total Registrations.

The # Reg Group is a variable that assigns a Group to each record based on the Total Registrations. The Total Registrations variable is just 2 other measures added together.

I would like to add another calculation - count of Accounts which should be the number of accounts for each Age Group and # Reg Group but I don't want to display the Account ID in the report. As soon as I remove the Account ID, all the data gets summed up and it shows wrong results.

I have tried changing the context, making the Total Transactions a dimension but nothing so far seems to be working.

My formula is =Count([Acct Id]) In ([Age Group];[# Reg Group])

Any help is much appreciated! Example is attached

context calc.txt.zip

[Former Member · Accepted solution]

Hi Maria,

Try the below options once.

Let the total registrations be a measure.

Variable for Reg Group= if(........) {forall(account ID)/Foreach(account ID)} ..... try both foreach and forall and see if it works

> count(account ID)= count(accountID) foreach(Reg Group)

[Former Member · Accepted solution]

Vinay,

is this what you mean?

= If([Total Registrations]=0;"0" ForAll ([Acct Id]) ;

If([Total Registrations]> 0 And [Total Registrations] < 5;"1-4" ForAll ([Acct Id]);

If([Total Registrations]> 4 And [Total Registrations] <11 ;"5-10" ForAll ([Acct Id]);

If([Total Registrations]> 10 And [Total Registrations] < 21 ;"11-20" ForAll ([Acct Id]);

If([Total Registrations]>= 21 ;"21+" ForAll ([Acct Id]);

"N/A")))))

I tired both FoirEch abd ForAll but it doesn't make any differentce.

[Former Member · Accepted solution]

Hi Maria,

The formula seems a bit incorrect as

condition 2 is contradicting with Condition 3

Condition 2 says TR<5

Condition 3 says TR >4

Try this formula.

=(if( TR=0) then 0 elseif(TR<5) then "1-4" elseif(TR<11) then "5-10" elseif(TR<21) then

"11-20" elseif(TR>=21) then "21" else "NA")foreach(accID)

=count(accID) in(Reg Group)

Regards,

Vinay Raja B

[Former Member]

Hi Maria,

Check the attached screenshot. Can you tell me whether you are trying to achieve this ? I am just trying to understand the exact Scenario.

Regards

Arijit

[Former Member]

Hi Arijit,

yes that's exactly what I am trying to achieve!

Maria

[Former Member]

Hi Maria,

Instead of

=Count([Acct Id]) In ([Age Group];[# Reg Group])

Please try:

=Count([Acct Id] In ([Age Group];[# Reg Group]))

and let us know if it resolves the issue.

-Ankush

[Former Member]

Hi Maria,

The same formula which you have suggested, should work.

What I am suspecting is that the datatype of the "Age group", "Reg group", "Account" may be a measure in your case, which aggregates the whole column.

One more improtant Thing you have to set the following Parameter on the webi table properties "Avoid duplicate row aggregation". Clcik on the screenshot.

Check this and let me know. If you have a doubt, you can upload a screenshot of the query Panel from webi.

Cheers

Arijit

[Former Member]

Isn't this the same thing?

[Former Member]

Arijit,

the problem is the Total Registration measure. I have set it to be a dimension but it doesn't act as a dimension and does get aggregated.  The # Reg Group variable is based on Total Registrations, the logic is:

= If([Total Registrations]=0;"0";

If([Total Registrations]> 0 And [Total Registrations] < 5;"1-4";

If([Total Registrations]> 4 And [Total Registrations] <11 ;"5-10";

If([Total Registrations]> 10 And [Total Registrations] < 21 ;"11-20";

If([Total Registrations]>= 21 ;"21+";

"N/A"))))

So what happens is that once I remove the Account ID from the table, the Total Registrations get summed at and grouped under the 21 + bucket, see below. I think if I get the Total Registrations to NOT aggregate, then the count of Accounts will work because that in turn is based on the # Reg Group..

[Former Member]

Hi Maria,

did you also try to activate the parameter "Avoid duplicate row aggregation" on the block properties as I mentioned in my last reply ?

Regards

Arijit

[nscheaffer]

These blog posts have helped me understand calculation contexts better.

Removing the Confusion from Calculation Contexts

Calculation Context Part I: Overview

Hope this helps,

Noel

[arijit_das]

Can you try to create a detail variable for Total Registrations and use the same in the logic for # Reg Group ?

[Former Member]

I tried that before and it did not work.

I need the Total Transactions variable to not aggregate so that the # Reg Group calculates at the Account ID and then the count of Account ID would be correct. 

Maria

[arijit_das]

Can you try the grouping in universe layer instead of webi layer ?

[Former Member]

I could but the users want to have the ability to do this dynamically in Web I.

Thanks, Maria