Webi 4.2 Calculate max(MEASURE) for next 10 days from first date

building reports webintelligence
#1

All,

i am a bit confused while creating a webi report and would appreaciate your help.

My colums are date (from datebase) and measure (calculated for each date in report) and i need to check the lowest value of the measure for the upcoming 5 days from the perspective of each day in the first column line. e.g.

column 1: date between 30.11.2020 and 17.12.2020
column 2: measure) for dates from 30.11.2020 to 17.12.2020
column 3: my expected results für next 5 days starting from each day in column 1. In

image

Any idea how to archieve this in Webi? So far i didn´t get it done in Webi like using relativedate(), min(), ForEach, IN …

Thanks and appreciated a lot

Ralf

#2

Hello,

Try using the where() clause.
I suppose that your bloc is ordered by ‘Date’, you can achieve the requsted result by doing:
=Min(measure) where(Date between(“30.11.2020”;“04.12.2020”))

Above the dates are given manually, but you can find a way to fill them automatically.

Hope that helps

#3

Thank you very much for your effort. I tried your suggested way but ended in really strange results. Have look. I guess that BO is missing a context on that calculation. Couldn´t not even explain it …

The formular is as follows:

=Min([Measure]) Where([Datum]Between(CurrentDate();RelativeDate(CurrentDate();10)))

The result is:

image

#4

include where-condition in the min-parenthesis
min ( … where … )

#5

Hello again,

The result is really strange, and i reproduced it with a sample data set.
BO does not make any difference between Min, Max and SUM using the formula above which seems to be a bug (else it will be very stupid as an expected behaviour)! values are always summed up when limiting the date!!!
The solution I found:
1- create a flag that takes 1 when date is limited between CurrentDate() and RelativeDate(CurrentDate();10)
2-recreate the formula using the flag:
MyMeasure=Min(measure) where(flag=1)
this will calculate the min correctly but only for the rows where flag = 1
Also you may use Min(measure in [datum]) instead of Min(measure) depending on your business need
3- Create a new variable which will take the min of your calculation in the block:
FinalMeasure = Min(MyMeasure) In (Block)

This solution wrked for me.

#6

Hi there. Sorry for not having answered yet. Your tips were quite helpful and i remembered how useful and often also the old forumtopics forum had been to me. Actually years ago i had more answers to provide to the community then i had questions … times change :wink:

Anyway … not really sure if that really gets me the results as the last measure doesn´t stop at the date where the flag is “1” as you adviced. My measures:

=If([Date]) Between (Min([Date]);RelativeDate(CurrentDate();30)) Then 1 Else 0
=Min([measure]) Where( [Flag für Min(Value)] = 1)
=Min([MinWert für Periode]) In Block

image
image496×756 7.07 KB

And thanks again ! Hope this forum will be as good as the old one !