0

I have a table with 3 columns in SAP Webi as shown in the picture.

In column "weekly average" I want to dynamically calculate the weekly average (from Monday to Sunday) of column "count".

Is it possible to do it with a variable? If not, any suggestions how to do it with other methods?

Thanks.

enter image description here

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Toli
  • 109
  • 1
  • 4
  • 13

1 Answers1

2

Yes, this can be done. Two steps: first create a variable to define a single value for each week, then another to return the average of those values.

For step 1, create a new variable ("Date Week") with the following definition:

=LastDayOfWeek([Date])

Then, in the report block (where your "Weekly Average" column is), create this formula:

=Average([Count]) in ([Date Week])

This should produce the result you want.

Joe
  • 6,767
  • 1
  • 16
  • 29
  • Thanks Joe, but the final result is the same as column "Count". Although when I drag "Date Week" into the table, it displays me what I want to have, but again the results are displayed in all rows (from monday to sunday). I want it to display the results only on those rows where it matches with the last day of the week from column "Count" (exactly as it is in the Picture). Any suggestions? – Toli Sep 19 '16 at 13:20
  • 1
    Sure, try: `=If DayNumberOfWeek([Date]) = 7 Then Average([Count]) In ([Date Week]) – Joe Sep 19 '16 at 14:02
  • Nothing is coming as a result. Anyhow, I am satisfied with first formulas and the outcome, even though it is not exactly what my management want to see. Thank you very much Joe. – Toli Sep 20 '16 at 12:04
  • Incredible, with 6 it is working and this is what we want to see. Kudos to you @Joe – Toli Sep 22 '16 at 08:17
  • It was my mistake - 7 is Sunday but you needed to show the date for Saturday, which is 6. – Joe Sep 22 '16 at 12:04