Creating a variable to calculate the Max value of two or more measures

rennerom · November 11, 2020, 3:25am

I have a report (WEBI 4.2 support pack 7) in a wide table format that calculates inventory projections by month for the next 12 months. Each monthly inventory projection is it’s own calculated measure. So I have a variable for Period 1 Inventory, Period 2 Inventory, all the way to Period 12 Inventory. I need to add another variable that returns the maximum inventory projection for a given material between Period 1 and Period 12.

So a sample report may look like this

Material	MaxValue	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12
aaa	10	1	2	3	4	5	10	6	5	4	3	2	1
bbb	17	3	3	3	4	17	2	2	2	1	1	1	1
ccc	11	1	2	3	3	3	3	4	4	11	9	7	6

I’m specifically interested in a more efficient way to find the Max value from multiple inputs. A number of programming languages have very simple ways to do this. But the WEBI Max() function only takes one required argument (the aggregated dimension) and one optional argument (a member set, if available).

In my example, I would think there must be a way to use something like

MaxValue = Max( P1, P2, P3, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12)

which would simply return the max value of the list of measures that were provided. I know that is not how the WEBI max function works, but I’m hopeful I’m overlooking something simple.

Right now I use a super long nested if statement. And it's only good for comparing 9 inputs.

MaxMeasure = If(
    [P1] > If(
        [P2] > If(
            [P3] > If(
                [P4] > If(
                    [P5] > If(
                        [P6] > If(
                            [P7] > If(
                                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
);
[P3];
If(
    [P4] > If(
        [P5] > If(
            [P6] > If(
                [P7] > If(
                    [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
)
);
[P2];
If(
    [P3] > If(
        [P4] > If(
            [P5] > If(
                [P6] > If(
                    [P7] > If(
                        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
);
[P3];
If(
    [P4] > If(
        [P5] > If(
            [P6] > If(
                [P7] > If(
                    [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
)
)
);
[P1];
If(
    [P2] > If(
        [P3] > If(
            [P4] > If(
                [P5] > If(
                    [P6] > If(
                        [P7] > If(
                            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
);
[P3];
If(
    [P4] > If(
        [P5] > If(
            [P6] > If(
                [P7] > If(
                    [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
)
);
[P2];
If(
    [P3] > If(
        [P4] > If(
            [P5] > If(
                [P6] > If(
                    [P7] > If(
                        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
);
[P3];
If(
    [P4] > If(
        [P5] > If(
            [P6] > If(
                [P7] > If(
                    [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
);
[P4];
If(
    [P5] > If(
        [P6] > If(
            [P7] > If(
                [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
);
[P5];
If(
    [P6] > If(
        [P7] > If(
            [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
);
[P6];
If(
    [P7] > If(
        [P9] > [P8];
[P9];
[P8]
);
[P7];
If(
    [P9] > [P8];
[P9];
[P8]
)
)
)
)
)
)
)
)

N8AKTIV · November 11, 2020, 8:18am

why not using one variable to derive the Periode Pxx from your data and set this as the column in the crosstab ?
set the period as 3-characters so it will show as P01 instead of P1, this will help with correct sorting of the periods
within a proper crosstab a simple max()-function should calculate the value for each line automatically

rennerom · November 11, 2020, 4:32pm

Thanks @N8AKTIV, those all make sense. And I have been able to use the max function as you described when applied to a crosstab.

There are a few conditions, at least for this report, that make doing so almost as difficult as creating that long nested if statement. The sample report from above is actually a small snippet of information that exists on that report. In addition to the Inventory projection measures by period, I also have forecasts by period and on order by period. Design wise, they need (should?) be in groups where P1-P12 Forecasts come first, followed by P1-P12 On Order, then P1-P12 Inventory Projections. (There’s also about a dozen other dimensions that are Material attributes, like brand, cost, category, etc…) I’m not sure how to do that in a cross tab. Like if I had my Period dimension as the header, and the body as Forecasts, On Order, and Inventory Projection, I think they will be organized and sorted by the header?

Something like this?

Period 1

Period 2

Forecast	On Order	Inv Projection
4	0	2
10	5	5
0	0	10

Forecast	On Order	Inv Projection
6	2	0
2	5	8
8	4	6

Is there a way around that?

Second condition, the inventory projections are not really inventory projections (I just simplified my example so it would be easier to follow what I’m trying to do). In this case they are dollars projected to be on hand 9 months from period N. So Inventory Projection for Period 3 represents on hand dollars remaining in Period 12 based on current on order quantities and current forecasts. This makes the calculation somewhat difficult to do when organized in a long format (lots of runningSums).

Lastly, we have a limit on the amount of records we can query. That limit is 1million. I am far over that limit when I organize this query to be long formatted. But I do have the ability to get around this when needed.

I totally get that WEBI has it’s limits and it won’t surprise me if there isn’t an easy analog for something like Python’s Max() function.

p.s. Your recommendation just hit me that using Pxx notation makes a ton of sense even outside of what I’m trying to do in this example!!!

N8AKTIV · November 11, 2020, 11:05pm

because of the multi-dimensional data and context-dependent calculation within a report, you can not
insert multiple values in a max()-function like in a programming-language.

the basis to calculate your max or running-sum has to be insert in the calculation-context of the function.
you can use “where” to restrict the data and you can use “in”, “foreach”, “forall” to add and remove attributes,
so the calculation will be independent of the placement within a report.

https://blogs.sap.com/2014/12/19/all-you-need-to-know-about-in-foreach-forall-context-operators/