So what I am looking for is the correct webi formula that will give me the max date where movement type is 202.
MaxDate is no problem but this now gives me 6/30/22.
But I want the max date for MvT 202 so that would be 5/31/22
Tried several variants however a simple :
=Max([Posting Date]) Where ([Movement Type] = “202”) would work.
But that still gives me multi values.
Yes 202 is not unique in the list but that is why I want the max date for MvT 202.
Note there are multiple Materials also. So maybe that is causing the problem.
I’d try =Max([Posting Date) in ([Movement Type])
Then it will return the maximum date for each movement type. If you only want the max date for 202 and no other movement type, you could then do something like
=If([Movement Type]=202; Max([Posting Date)In([Movement Type]);"")
but I may have misunderstood what you need there!
If you need the max date split by the Material - key too, then try
=Max([Posting Date) in ([Movement Type];[Material - Key])
or
=If([Movement Type]=202; Max([Posting Date)In([Movement Type];[Material - Key]);"")
Unfortunately no luck @slinkywhite
The first line works fine, it gives me the max date.
But still the part to give me the max date belonging to movement type 202 is not working.
For your reference 202 is a text field so I need to use “202”.
Maybe some screens of the 2 sources could shed some light:
Posting Date Addendum is now the normal max date. It works.
But I need to get the max date of Movement type (that you see in the second universe) 202.
Because for the yellow line the correct date is 5/31/22 and not 6/30/22
if only [Movement Type] is your key to get the max. date, you have to remove [Material - Key] from the context of your formula and calculate this within another variable
Thanks @N8AKTIV
But I am afraid I don’t understand what you mean.
About what formula are we talking?
And what should I calculate with another variable?
@MarkP well the table I use is only an example to show that there are multiple dates for multiple Movement Types.
I want for every line op material/addendum part the max date belonging to a certain movement type I set in the formule (now talking about 202 but another time I will make a formule for 201 etc.)
As you see here in yellow the max date is 6/30/22 for material APL2114536.
But this is the max date for all dates for this material.
The one I am looking for is 5/31/22 which is the max date for movement type 202 of material APL2114536.
Note I have 2 posting dates in seperate universes. To explain better I justed named it Posting Date.
This is the code I used. Maybe that specific posting date is causing the problem?
=Max([InvAddendum].[Posting Date] Where ([Movement Type]=“202”))
Ah, okay. So we’re dealing with differences in granularity between queries that you’re trying to put together. Be very careful - you won’t get a Posting Date to match with a Movement Type if they’re from different queries and aren’t in both query. Is it possible to bring Addendum Part into the second query - that way you’re getting the Posting Date at the part level, which would make sense to Webi.
I linked the output of that query to the first (main) query.
The addendum part numbers show in that second query as material.
So field material in this query will be only filled with the addendum part numbers from query 1.
(also plant is linked but that is always the same)
You see in the second query there is posting date and movement type.
I used a merg like:
There is a link with a 3rd query in there but for this field that I am trying to calculate it is not in scope.
The end result we want to look like this:
So here the first date now it the max posting date for a addendum part.
In 90% of the cases this will be ok but to make it a 100% we want to look for the max date of a certain movement type.
Not sure what you mean with bring addendum part to the 2nd query.
The end report we want is a combination from data out of all queries.
I suggested bringing date in because you can’t use dimensions from two queries if they aren’t merged.
Simply making them a detail won’t work if they aren’t a one-to-one relationship with the owning dimension as that’s not how details work.
As you have Movement Type/Posting Date from one query and Addendum Part from another and Webi doesn’t know their relationship (other than having the same owning plant), they will have a different usage, i.e. context, hence the #CONTEXT error that you get.
Which is why I was wondering if it is possible to get all three in the same query.
If your queries are merged at a level where you only have one addendum part per movement type, you might be ok. An alternative that might work:
Create a max(table.postingdate) measure in your universe. Use this in a query at the right level of granularity so that you’ll only get one row per part and movement type. Obviously without knowing the database it’s not a definite but it might be possible.
At the moment you’re asking the equivalent of maximum number of doors on a motorbike when you sell all sorts of vehicles and it can’t handle the small out of context part of your query.
But does not change anything to the logic)