BusinessObjects Board

Help with ForEach - Where forumulas

building reports
#1

Hi,

Hoping someone can help.

I have a report where I trying to pull back a text value for the latest invoice date.

I am trying to summarise some billing data by the following columns:

Product, Core Value (which is Core, Non-Core or Unassigned), Value £

The Core Value could be one of the 3 values listed above, but I would like to summarise the data based on the value that was on the very latest invoices.

So for instance:

Raw Data

Product | Billing Date | Core Value | Value £
a1 | 01/01/2023 | Core | £2
a1 | 03/01/2023 | Non-Core | £2
a1 | 05/01/2023 | Non-Core | £2
a2 | 12/12/2022 | Non-Core | £10
a2 | 01/01/2023 | Non-Core | £10
a2 | 04/01/2023 | Core | £10

Summarised Data

Product | Core Value | Value £
a1 | Non-Core | £6
a2 | Core | £30

I have got the max invoice date using the following formula:

Max Invoice Date: = Max([Spend].[Billing doc. date] In ([Spend].[Material].[Material - Key];[Spend].[Billing doc. date])) In ([Spend].[Material].[Material - Key])

I then try & get the Core Value for the maximum invoice with the following formula. But this only shows correctly against the right date when I pull the billing doc date through (the value against the rest of the dates are blank), and then when I remove the Billing Doc Date I get #MULTIVALUE

Core Value per Max Date =[Spend].[Customer Core Mat] ForAll ([Spend].[Material].[Material - Key]) Where ([Max Invoice Date] = [Spend].[Billing doc. date])

Can anyone tell me where I am going wrong? Any help would be greatly received, as it’s been driving me crazy trying multiple different things.

Regards
Carly

#2

Hi Carly

ForAll removes items from the calculation while ForEach adds items to the calculation.

I’m not clear on what your desired Summarised Data would look like - is the Non-Core for a1 because Non-Core is the type of the most recent invoice for that Product?

What is Material - Key?
What is Material?

Are either of those Product?

If you wanted to produce the Summarised Data table based on the Raw Data, you’d need the following, based on the headings in Raw Data being the object names:

=[Product]
CORE VALUE - SEE BELOW
=Sum([Value])

As you can guess, the the first and last ones are easy as they simply add up when you remove the other objects.
If I understand correctly, your Core Value in the Summarised Data should the Core Value entry for the most recent Billing Date in that Product. On that basis, here’s how I’d calculate it:

=Max([Core Value]) Where ([Billing Date] = Max([Billing Date]) in ([Product]))

2 Likes
#3

THANK YOU SO MUCH!

This has worked first time. I can’t tell you how many formulas I have tried. These types of formulae always catch me out.