BusinessObjects Board

Need to count how many times a certain key occurs

Well I again are struggling with counting things in webi…

This is the situation:

So this is filtered on the specific key so the solution should also work when the filter is off.

I have deliveries and on these deliveries there are material. But also a Log Month.
What I want it to only count the quantity once.
Thought I already had that covered with looking at the max log month and only put the quantity there

But as appeared a delivery could also be be done double for only a few materials.
Therefore I had to go a level deeper on material.
I made a key combining delivery and material.

So in this example the key occurrs twice so the ‘CountMat’ should give 2 and not 1
When there is only 1 log month for a key it should give 1.

Tried several count option, for not this one is :
=Count([Variables].[Key]) ForAll ([Variables].[Delivery])

Could anybody put me on the right track?

Think I had to come here and “complain” earlier :laughing:

Seem to got it working with:
=Count([Variables].[Key];All) In ([Variables].[Key])

And of course this now looks totally logical :joy:

Ok so now I need to figure out how to make MthChk hide when logmth is not max logmth but only if CountMat > 1…

Ok got by that also with:
=If ([CountMat] = 1) Then “Show”
ElseIf ([LogMth] = Max([LogMth]) In ([Variables].[Delivery])) Then “Show”
Else “Hide”

But this thing keeps giving me headaches…because of course I want to hide these zeros.
In the formatting of the block it will not work. Guess because MthChk also needed to be a measure field for some reason.
But putting a filter on Mthck hides the lines with “Hide” but is screw up the total!


Why??? And what is up with these blanks??
Is it the formula not working anymore and therefore I lost 380 units?

Even worse gets it when I want to remove all detail fields:
Or when I remove the filter again:

:dizzy_face: :sob:

Ok done some more analysing.

First of the Amount comes from a 2nd query and the other fields form 1st query.
These queries have been merged like this:
The key is not very special on both queries it is delivery+material

Then my initial Quantity is set up like this:

I found that the Field ‘Key’ is causing the problem.
As soon as I take it out of the layout amounts are screwed.

As you see the amounts for mth 8 are set to 0 and the total has a multivalue.

What is causing this??