Multi Value when using ForEach despite of aggregate function

building reports webintelligence
1

For a report I am creating for my project at a clothing company I have the following situation. I need to compare sales to budgets, but budgets are not always specified as much as sales.

I have sales quantities on a season, customer, gender and pg level.
My budget is also on this level, but for some product the gender is set to “all”.

So lets say that i have the following sales (level retrieved from query is lower)
Season | Customer | pg | Gender | Quantity
2009/1 | customer1 | g1 | Men | 1
2009/1 | customer1 | g1 | Women | 2
2009/1 | customer1 | g2 | Men | 4
2009/1 | customer1 | g2 | Women | 8
2009/1 | customer2 | g2 | Men | 16
2009/1 | customer2 | g1 | Women | 24

I have the following budget
Season | Customer | pg | Gender | Quantity
2009/1 | customer1 | g1 | All | 2
2009/1 | customer1 | g2 | Men | 8
2009/1 | customer1 | g2 | Women | 4
2009/1 | customer2 | g2 | Men | 16
2009/1 | customer2 | g1 | Women | 24

In my report i want to assign sales (gender = men, women) which have a non gender specific budget (gender = all) to the gender “all”

The report I want to create is as follows:

Section Men
Season | pg | Sales | Budget
2009/1 | g2 | 24 | 20

Section Women
Season | pg | Sales | Budget
2009/1 | g2 | 4 | 8
2009/1 | g1 | 24 | 24

Section All
Season | pg | Sales | Budget
2009/1 | g1 | 3 | 2

Table2
Season | Gender | Sales | Budget
2009/1 | Men | 24 | 20
2009/1 | Women | 28 | 32
2009/1 | All | 3 | 2

At the moment I have the following
3 queries

  1. sales
  2. budgets
  3. budgets non gender (
    select Season, Customer, pg, Quantity
    from budget
    where gender is “all”

In me example this will give me the following row
Season | Customer | pg | Quantity
2009/1 | customer1 | g1 | 2

Sales and budget queries are linked on the lowest level possible being;
Season, Customer, pg, Gender

Sales and budget non gender queries are linked on the level;
Season, Customer, pg

My idea was to determine gender specific sales quantity by doing the following:
Sum( If IsNull( [budget non gender].[Budget Quantity] ) Then [Sales].[Sales Quantity] Else 0 )

However if I have a table which is not on the lowest level, then this does not work. So i thought I try the foreach fuction.
I first created a variable called budget_key
= Season + “.” + Customer + “.” + pg + “.” + Gender

And then changed my formula to:
Sum( If IsNull( [budget non gender].[Budget Quantity] ForEach ([budget_key]) ) Then (If IsNull( [Sales].[Sales Quantity] ) Then 0 Else [Sales].[Sales Quantity]) Else 0 )

This works fine when I have a table containing all of the field which are part of the budget key. Like so:
Season | Customer | pg | Gender | Sales | Budget1 | Budget2 | Spec-Sales
2009/1 | customer1 | g1 | Men | 1 | NULL | 2 | 0
2009/1 | customer1 | g1 | Women | 2 | NULL | 2 | 0
2009/1 | customer1 | g2 | Men | 4 | 8 | NULL | 8
2009/1 | customer1 | g2 | Women | 8 | 4 | NULL | 4
2009/1 | customer2 | g2 | Men | 16 | 16 | NULL | 16
2009/1 | customer2 | g1 | Women | 24 | 24 | NULL | 24
2009/1 | customer1 | g1 | All | NULL | 2 | NULL | 0

However when I create a table at a higher aggregation level, i get multi value error.
Season | Customer | Sales | Budget | Spec-Sales
2009/1 | customer1 | 15 | 14 | #MULTVALUE (should be 12)
2009/1 | customer2 | 40 | 40 | #MULTVALUE (should be 40)

So in short!
I need to calculate specified sales on the “Season | Customer | pg | Gender” level and aggregate these values to higher levels.
Can anybody offer any assistance?

I already tried placing the ForEach function in a different part of the formula but this does not solve the problem.

mdniet (BOB member since 2008-08-13)

2

Long description, am not sure about your requirement …

Can you attach screen shot of that for better understanding…

Thanks

BOCP (BOB member since 2007-07-02)

3

Hello midniet!

Have you tried to take an aggregate of your aggregate, which is needed for the #MULTIVALUE error? For instance, wrap the formula you list above in another sum() function:

Sum(Sum( If IsNull( [budget non gender].[Budget Quantity] ForEach ([budget_key]) ) Then (If IsNull( [Sales].[Sales Quantity] ) Then 0 Else [Sales].[Sales Quantity]) Else 0 ))

This takes all of your individual aggregates (when all the fields are displayed) and aggregates them together.

Please see if this works and please let us know!

Cheers!

Ryan Bierschenk :us: (BOB member since 2009-01-26)

4

Ok here are some screenshots

This is illustration of the problem. (i can;t seem to recreate my production problem with multi value, but the problem stays the same essentially)
Below you see the report (figure1) and you see that specified and non specifed sales are correctly filled in the first table based on the non specified budget.
Non specified budget is a selection of form the budget table where gender = All. this query is merged with the sales query on season, customer and productgroup.
However in the second table it totally disregards the calculation.
Spec Sales Qty => =Sum( If IsNull([NonSpecBudget].[Sum of Budget Qty] ) ForEach( [budget_key] ) Then [Sum of Sales Qty] Else 0 )
NonSpec Sales Qty => =Sum( If IsNull([NonSpecBudget].[Sum of Budget Qty] ) ForEach( [budget_key] ) Then 0 Else [Sum of Sales Qty] )
budget_key => =[Season]+"."+[Customer]+"."+[Pg]+"."+[Gender]
The requirement is, that it calculates the first two variables on the level of the third variable. Thus when I aggregate the totals will match regardsless of aggregation level.


(figure1)

This report is calculated from the sales table (figure2) and the budget table (figure3)


(figure2)


(figure3)

mdniet (BOB member since 2008-08-13)