Cannot filter dates at a document level

building reports desktopintelligence
1

I’ve been trying to create a logic that would help me filter the report at a document level (so I can use the same variable to influence other reports).

Basically I want to leave out everything but the very first delivery date of a given purchase order. This is what I did:

First I created a logic to pick the Min Date for all scheduled deliveries

=Min([Sch. line del. date]) ForAll([Purchasing document];[PO Item];[Sch. line del. date])

Then, if the scheduled delivery was the same as the Min Date for that PO, post the delivery date, else post the letter “b”

=If([Sch. line del. date]=[MinSch];[MinSch];"b")

This is the result:


Purchase Order   Item     MinFlag      Scheduled Del     Part
4300002319       10        9/1/2015    9/1/2015           44022-X
4300002325       10        6/16/2015   6/16/2015          44022-X
4300002325       10        b           12/8/2015          44022-X
4300002325       10        b           3/29/2016          44022-X

The problem is when I try to filter. Instead of getting a list of dates, I’m getting this (screenshot at the bottom) where there are no lists of dates but a single date along with the “b”. When I pick to be “not in list” the entire report turns blank.

Again, at a report level, it seems to work. It is at a document level that the whole thing goes south.

Any help would be appreciated.
example.PNG

Baldomero (BOB member since 2017-02-03)

2

That’s because the Min() function produces a measure object, and in Deski if you use a measure object in a formula or variable the output is automatically another measure. You cannot filter on measures (unless the measure is a universe level object and you use the global filter function).

The only way round this in Deski is to create an object in the universe which replicates the logic of your initial Min() object but qualify the output as a dimension.

Alternatively, move the report to Webi. It’s a bit of a hack, but you can change the output type of a formula to not be a measure provided you don’t include any aggregate functions in the formula.

Maddye :uk: (BOB member since 2009-01-09)

3

Thanks Maddye, unfortunately that confirms what I suspected. I was trying really hard to avoid having to work with the backend logic as I have to contact another team within my group (I know, quite silly) and that takes forever to get done. :hb:

Baldomero (BOB member since 2017-02-03)

4

Let’s wind back from the solution for a second.

There are potentially other ways round this.

Firstly, what version of what product are you on? Only you’ve posted Webi code in the Deski forum.

Secondly, can we assume that the raw data is as per the result table but without the MinFlag column?

Thirdly, please describe what you’d like as your end result (e.g. the first two rows of the result table).

Thanks,
Mark

5

Hi Mark,

Thank you for taking the time to reply. There is no way around at the front-end. Like Maddye said, I’m dealing with a measure rather than a dimension, hence I can’t use it at a document level, only at report level.

Getting this logic to work is just the beginning of my project as I’m looking to have many reports to branch out from this logic. Anyhow, building a logic at the backend and bring it over as a dimension seems like the only solution.

Baldomero (BOB member since 2017-02-03)

6

Idea:

Create a measure object, SchedDateNumber as
=ToNumber(FormatDate([Sch. line del. date];“yyyyMMdd”))

Add a ranking to your table to keep the bottom 1, ranked by Purchase Order.
This will keep the earliest date for each Purchase Order.

7

Hi Mark,

This logic certainly works, but unfortunately if I were to try to filter everything but the very first date at a document level, BI won’t see the list of dates as the field is a measure rather than a dimension.

Baldomero (BOB member since 2017-02-03)

8

I don’t follow.

My advice is to use the podium icon to invoke the ranking dialogue box, not to create a filter.