Cumulative Quanities with a filter applied

building reports desktopintelligence
#1

I have a list with Material, Batch, Quanity, posting date.

User wanted to see only the last line of the posting date.
I did this with a ranking on posting date and then apply a filter to the report only showing the 1’s.
Works just fine.

Now the user wants to see the Quantities for all the posting dates by Material/Batch combi (I made a key that adds material and batch together).
So like a YTD quantity.

I easily fixed that with this formula:

=RunningSum([Quantity]) ForEach([Key])

Also that worked just fine…untill I put back my filter.
The amounts were wrong.

Tried fixing with:

=NoFilter(RunningSum([Quantity]) ForEach([Key]);All)

or

=NoFilter(RunningSum([Quantity]) ForEach([Key]);Drill)

But no luck.
Anybody know the solution?

rpinxt (BOB member since 2019-01-23)

#2

NoFilter removes all filters - you might need to add a Where or IF statement to reapply filters that you do want to keep.

Mark P :uk: (BOB member since 2003-02-03)

#3

What filter to put back would that be?

Key is the only other variable in play I would think.

Do I need to do something with Key then?
But I already have a for each key there.

rpinxt (BOB member since 2019-01-23)

#4

Which BO Version are you on?

Mark P :uk: (BOB member since 2003-02-03)

#5

We are on BO 4.2 Support pack 7.

rpinxt (BOB member since 2019-01-23)

#6

What objects are in your table?

Mark P :uk: (BOB member since 2003-02-03)

#7

Ok so I have, Plant, Material, Batch, Posting Date, Quantity

Then I have variables:
Key :

=[Material]+"* B *"+[Batch Number]

Cum.Q :

=RunningSum([Quantity]) ForEach([Key])

Rank :

=Rank([Posting Date];Top;([Key]))

So as said this all works just fine.
Until… I put a filter on Rank because we only want to see the line with the latest posting date.

But by doing that the Cum.Q is influenced and Cum.Q will equal Quantity if only the line with Rank 1 is showing.

rpinxt (BOB member since 2019-01-23)

#8

If you’re just showing the most recent line, can you just use Sum instead of RunningSum; something like

=NoFilter(Sum([Quantity]) in ([Key]))

Mark P :uk: (BOB member since 2003-02-03)

#9

Yes that does work :smiley:

Thanks for giving me this different approach to look at it.
This code will give on every line the grand total.
But indeed if you only will show 1 line per key at a time that will give for that line the correct grand total.

Thanks again!

rpinxt (BOB member since 2019-01-23)

#10

You could get creative and use the two different formula in an if statement based on what data you are displaying. For example, you could use an input control to switch between the two table layouts to display most recent or all.

Mark P :uk: (BOB member since 2003-02-03)

#11

Thanks for the suggestion.

When the user makes a move that way I will keep this in mind :wink:

rpinxt (BOB member since 2019-01-23)