Combined Alternate States and Aggregation Question

[#1]

Hi,

I came across a challenging QV problem today and was wondering if anyone could help. I'll give a simplified example to explain:

Let's say that I have a dataset with the following fields:

USER (with values 'Alpha', 'Beta', 'Gamma')

CURRENCY (with values 'GBP', 'USD', 'EUR')

MONTH (with values 'Jan', 'Feb', 'Mar')

PRODUCT (with values 'A', 'B', 'C')

SALES (a metric, with integer values between 1 and 100)

The dataset is preaggregated across the first four fields, so has 3x3x3x3 = 81 rows (1 per unique combination of those values).

I want to be able to compare the following sets:

{A} - Filtered down to a single user (e.g. 'Alpha') and a month of our choosing e.g. ('Jan')

{B} - An average across all users, for a month of our choosing that can be different from {A} e.g. ('Feb')

Any selections in CURRENCY and PRODUCT should apply to both {A} and {B} equally.

If I want to just compare the two values, this is achieved by doing the following:

{A} = {Sales for Alpha in Jan} = SUM({$<MONTH = StateA::MONTH, USER = StateA::USER>} SALES) = 406

{B} = {Average Sales for all Users in Feb} = AVG(AGGR(SUM({$<MONTH = StateB::MONTH, USER = StateB::USER>} SALES),USER)) = 379

The difficulty arises when I want to display these expressions in a chart that has PRODUCT as a Dimension.

I created a Straight table chart with PRODUCT as Dimension, A and B as expressions, and expected to get the following:

Product	{Sales for Alpha in Jan}	{Average Sales for all Users in Feb}
A	103	152
B	151	104
C	152	123

Instead, what I got was:

Product	{Sales for Alpha in Jan}	{Average Sales for all Users in Feb}
A	103	379
B	151	-
C	152	-

Interestingly, if I make a single selection in PRODUCT then the table filters down to a single row showing the correct value in both columns. It seems that some of the Dimension logic going on under the bonnet is struggling to deal with the alternate states...

Any ideas on what's going on here or how I could do this correctly? I've attached the dataset spreadsheet and an example qvw if you're interested in helping out. I've been stuck on this for a while - any time you could spare would be hugely appreciated!

Many thanks,

James

[Anonymous]

Do you need to AGGR over PRODUCT as well :

     AVG(AGGR(SUM({$<MONTH = StateB::MONTH, USER = StateB::USER >} SALES),USER, PRODUCT))

[morganaaron · Accepted solution]

Confirmed this fixes your expression.

If you wanted to avoid the aggr statement, you could use:

SUM({$<MONTH = StateB::MONTH, USER = StateB::USER >} SALES) / Count({StateB} Distinct USER)

[#4]

Hi Aaron - that worked perfectly, thanks very much!

Any idea why AGGR didn't work as expected?

[morganaaron]

Sorry, I probably phrased my reply badly! What Bill suggested fixes your expression, you needed to have Product as a second dimension that you were aggregating over - my fix is an alternative that avoids needing to use AGGR.