Using webi ForEach function to solve complex aggregation problems

When you create variables in web intelligence, they must be assigned a qualification. This qualification assigns a “strong” type of either a measure, or a dimension (or a detail) kind of object to your variable. This strong tying has a profound impact on how webi handles aggregation of values in the variable you create, and can be the cause of many headaches for report developers trying to handle complex reporting requirements. The following example uses the e-Fashion universe for illustration purposes:
Imagine you need to produce a report that contains a reporting year in column one. Column 2 should display the revenue for the year in prior year, and a revenue state for the current year, and the revenue by state in the third column, for the current year only. Seems fairly simple, right. You can create a fairly simple variable in webi to satisfy the condition for column 2: If([Year]=”2006″;[State];[Sales revenue]) (If the year is 2006, the current year, display the State, else, display the revenue). Your variable will have to have a qualification, and will default to a dimension most likely. When you place your variable in the report, you will see a result far from what you were looking for:

What is happening is that webi is treating the variable value as a dimension and is not aggregating it across the states for the prior years. So even though the states are not displayed, we still get multiple rows for the years, as if the states were included in the calculation context.
Try to change the variable qualification to a measure, and you get the flip side of the problem: now you get the infamous #MULTIVALUE error because webi is treating the list of States like a measure and is unable to aggregate multiple States per Year.

What can you do? Using the ForEach webi function, you can enforce the proper calculation context for each part of your variable and “loosen” the tight typing of your variable due to its qualification:
If([Year]=”2006″;[State];[Sales revenue] ForEach ([Year]))
Make sure you set the variable qualification to a dimension, and now webi will be able to properly calculate the proper aggregation for each year, as expected.

Add a similar formula as your third column variable (If([Year]=”2006″;[Sales revenue] ForEach([State]);””)), apply some formatting (with NumberFormat()), and there you go, requirement met.

This entry was posted in Web Intelligence and tagged . Bookmark the permalink.

52 Responses to Using webi ForEach function to solve complex aggregation problems

  1. Ravindra Saxena says:

    Hi,

    I’m facing an issue while calculating Skewness of sales in which, contribution of last week sales of the month to the total monthly sales is calculated. how can the last week’s sale(Last 7 days of the month) can be differentiated in order to get the desired result on monthly basis.

    Thanking in anticipation

    Ravindra Saxena.

  2. Victor says:

    I have an issue, can someone help me?
    =Count([Open Date])Where(Not(IsNull([Open Date]))) = 200 as expexted,
    But some of these open dates are the same so I need a count all of the dates.
    I need something like this below, but it is not giving me correct answer
    =Count([Open Date];all)Where(Not(IsNull([Open Date])))
    or I tried below as well and it didn’t work either
    =Count([Open Date]Where(Not(IsNull([Open Date]));all)

  3. Hellen says:

    Hello all,

    I get stuck in this report, so please help me if you can.

    I have 2 data tables:
    Tab1
    OrderID—–OrderDate—–ShipDate
    1————-1/14/17——-2/18/17
    1————-1/16/17——-2/18/17
    2————-2/14/17——-3/16/17

    Tab2
    OrderID—–ShipDate—–ReceiveDate
    1————-2/18/17——-2/20/17
    2————-3/16/17——-3/22/17

    I would like to have 2 tables into 1 table like tab below. This table have only the max OrderDate and eliminate all the other dates under same OrderID

    OrderID—–OrderDate—–ShipDate—–ReceiveDate
    1————-1/16/17——-2/18/17——-2/20/17
    2————-2/14/17——-3/16/17——-3/22/17

    I do the break on OrderID, then rank on OrderDate, somehow, I still not get it right. the ReceiveDate missing for the Order which have multiple OrderDate.

    Here is what I got:

    OrderID—–OrderDate—–ShipDate—–ReceiveDate
    1————-1/16/17——-2/18/17——-#ERROR
    2————-2/14/17——-3/16/17——-3/22/17

    Any suggestion would help!

    Thanks,

    Hellen

    • biha8964 says:

      Is the reason you have two tabs because you are using two queries? Or one query which goes to two different contexts? Or is all the data coming from a single query? In any case, you if you get multiple dates for each order ID, you will need to use some kind of a method to have just one date, as that seems to be what you are looking for. So either use an aggregate function (like max, min, etc..) to get one date per order ID, or some kind of filtering (like where in the webi function) to apply some filtering logic…

  4. Audrey says:

    Please help me if you can.
    I created “POF Location Code 2” variable with this formula : =If([Actual Failure %]ForEach([POF Location Code])<5) Then "Others" Else [POF Location Code]
    But I only want one entry for “Others”.
    How do I show all “Others” as one cell value only for Actual Failure %?

    Actual Failure % POF Location Code 2
    33.71325735 CANH
    30.69136173 ICNR
    18.37132573 KIXH
    7.168566287 PVGRT
    4.9940012 Others
    1.897120576 Others
    1.192261548 Others
    1.00479904 Others
    0.269946011 Others
    0.202459508 Others
    0.164967007 Others
    0.127474505 Others
    0.059988002 Others
    0.037492501 Others
    0.014997001 Others
    0.014997001 Others
    0.014997001 Others
    0.014997001 Others
    0.014997001 Others
    0.0074985 Others
    0.0074985 Others
    0.0074985 Others
    0.0074985 Others

    Thanks!

    • biha8964 says:

      If you need the [POF Location Code] dimension in the calculation context, and there are different [POF Location Code] values for “Others”, i do not see how you can avoid that.. Will need more data prep work in the DB/Universe. Otherwise, you can use an aggregate function to aggregate the results across the dimension that yields either “Others” or value, and that should be fine..

  5. faye says:

    I have been working on a specific report for months and always am exporting to Excel to do vlookups. My scenario: A patient who is receiving Home Health in the community comes into the Emergency Department multiple times throughout the year. I want to see how many ED visits this patient has while the Home Health referral is open.

    My initial formula is simple:
    =If([ED Arrival Date] >[Referral Start Date]) And ([ED Arrival Date]<=[Referral End Date]) Then 1 Else 0

    The problem I am having is that a patient may visit the ED multiple times AND have multiple Home Health referrals open. I would usually bring my two queries (separate universes for ED and Home Health), and do a vlookup to get my sums.

    I have tried to do this in Webi, but was having the issue of #MULTIVALUE. I believe I have found a workaround for this, but am still struggling with the issue of each ED visit only looking at one referral, not all of them.

    Patient A:
    Referral Start | Referral End
    04/12/2015 | 08/04/2016
    08/04/2016 | 05/04/2017

    ED Visit
    02/04/2016
    30/07/2016
    24/08/2016
    27/09/2016

    What I should see is that all ED visits should have a count of 1 (as all of them fall within a referral start and end) for a total of four ED visits. What I am getting, however, is 1, as Webi is picking and choosing which referral to link to:

    Patient A:
    Referral Start | Referral End | ED Visit | ED during?
    04/12/2015 | 08/04/2016 | 30/07/2016 | 0
    08/04/2016 | 05/04/2017 |02/04/2016 | 0
    08/04/2016 | 05/04/2017 | 24/08/2016 | 1
    04/12/2015 | 08/04/2016 | 27/09/2016 | 0

    I have also tried the ForEach formula but then run into the #MULTIVALUE error.

    Any help would be greatly appreciated!
    Faye

    • biha8964 says:

      I think maybe something along these lines..: First, create a formula to calculate the min referral start date aling these lines: RefMin = min([Referral Start]) in ([Referral ID};[Patient])
      You need to include all the relevant dimensions in the “in” argument. I assume there is some sort of “referral id” you can use to tell one referral from another..?
      Then, do the same for max date: RefMax = max([Referral End]) in ([Referral ID};[Patient])
      Finally, you should be able to create a calc that counts/sums dates between each of those, along these lines: Count([Ed Visit];distinct) where [Ed Visit] between [RefMin] and [RefMax]… You may still need to add calculation context dims in the final formula.. Of course you will need to weak this per your universe, etc, but hope this helps with the thinking..

  6. padma says:

    Date Data Privider1 Dataprovider2
    1/2/2016 ABC
    1/3/2016 CDE FGH
    1/3/2016 CDE IFG

    I want to display data in Cross tab in SAP Business Objects report.

    Monday Tuesday Wednesday Thu Friday Saturaday Sunday
    2 ABC

    3 CDE FGH IFG

    I am geting Mutivalue error beacause for one date,there are multiple rows.

    Could any one help me

  7. Akshat says:

    Hi ,

    My data in a report is like this

    id name city manager’sId mgrName mgrCity

    I only have one city column and have to dynamically get manager’s city data
    i have the data but how to display it?
    i tried using foreach “=[Work City] Where ([ID]=[ManageID]Foreach([ID]))” and
    [Work City] Where ([ID]=[ManageID]in([ID]))

    but it doesn’t work and displays blank data
    any idea?

  8. BobL says:

    Thank you, biha8964, but I’m looking for the value associated with the max date and not just the max date itself.

    I figured it out. Here is one way.

    Variable 1: Max Date
    =Max([Service Date]) In ([Org Name])

    Variable 2: Is Max Date
    =If ([Service Date])=[Max Date] ForEach([Org Name]) Then 1 Else 0

    Variable 3: Top Value
    =[Service] Where([Is Max Date]=1) ForEach([Org Name])

  9. BobL says:

    Hello,

    There are posts close to this but not quite.

    I have a report that is by [Org Name] which includes a [Service] and [Service Date]. It looks like this:

    [Org Name] [Service] [Service Date]
    A BP 5/6/2016
    A BMI 6/6/2016
    B COL 1/1/2016
    B BP 9/5/2015
    C ENDO 11/3/2015

    I want to return the [Service] that is associated with the latest [Service Date] by each [Org Name].

    So the results would be:
    A BMI 6/6/2016
    B COL 1/1/2016
    C ENDO 11/3/2015

    I can get the Max date for each [Org Name] using:
    (VAR MaxDate) =Max([Service Date] ForEach([Org Name])

    Then I figured that I’d pull the [Service] using Where()

    I know that a Where() operator must have a constant on the right side so I created a variable:
    (VAR If Date is Max) =If [Service Date] = [MaxDate] Then 1 Else 0

    Then I created another variable:
    (VAR TopService) =[Service] Where ([If Date is Max] = 1) ForEach([Org Name])

    But TopService just gives #MULTIVALUE for A and B. C is OK since it only has one value.

    Any help is appreciated.

    • biha8964 says:

      Hi, i could be missing something, but i think all you need to do is this:
      Column1 in the table defined as =[Org Name]
      Column 2 in the table defined as =[Service]
      Column 3 in the table defined as =Max([Service Date]) in ([Org Name];[Service])
      This should produce a table with the org, service and max service date for each org/service combo.

  10. Jeff H says:

    Hello, hopefully someone can help me with this problem. It is quite similar to other questions but just a little different. I am trying to average a column based on the day of the week.

    Table:
    Day of week | Date | Orders | Shipments

    Monday | 1/4/16 | 3000 | 3500
    Monday | 1/11/16 | 3200 | 3700
    Monday | 1/18/16 | 3300 | 3900

    My data looks like the above and I am wanting to get an average based on the week day so with this data it would look like

    Monday | 3166 | 3700

    I tried using a formula like this : =If(DayName([Query 2].[Invoice Date]) = “Monday”;Average([Query 2].[Count of Orders]))
    but it just returns the #MultiValue error.

    Please help!

    Thanks in advance

    • biha8964 says:

      You are close Jeff, try this: =Average(If(DayName([Query 2].[Invoice Date]) = “Monday”;[Query 2].[Count of Orders];0))

  11. Dharma says:

    Hi All,

    Can some body help me out on how to apply filter only on Grand Total > 1000 when their is huge list of Monthly sales in cross tab as per the below example list.

    Values 2016-3 2016-4 2016-5 2016-6 Grand Total
    Sales $41,227.34 $36,929.39 $40,122.03 $22,018.35 $233,760.81

    • Ron Keler says:

      If i understand the question, you are asking how to apply a filter on a measure value at a particular level of granularity. Have you tried using a formula like =(sum([your measure]) in ([dim1];[dim2]..etc)) >1000
      where the dims combo will be the dims needed in your calc for the required aggregation level? this function is a boolean and will return true or false, which you can then use to filter your block in webi…

  12. Y.Venu says:

    Hi EveryBody,
    I have a requirement,the requirement as follows:
    I have to create a Crosstab Table where is holds data like example

    CY |PY |Total
    25|16,000 | 20,000|36,000
    32|18,000 | 15,000 |33,000
    40|50,000 | 30,000 |80,000
    55 | 80,000|40,000 |1,20,000
    ————————————-
    Sum|1,64,000|1,05,000|2,69,000

    Here 1st 2 rows indicates Sales of Current Year and Sales of Previous Year
    3rd row indicates Discount of Current Year and Revenue of Previous year
    4th row indicated Margin of Current Year and Margin of Previous year

    Here from table we can see the CY value and PY value of Sales ,Discount,Margin is different,what is the logic behind it.Data coming from Same Universe.
    I have done Some home work on this
    I have created three Queries that comes from same Universe with Same Objects with CY,PY and one Query have Sales ,other Query have Discount and other have Margin
    from here i thought of merging the tables and convert into Cross Tab Table.

    anybody providing solution highly appreciated.
    If the solution meets the requirement ,the other question is related to dynamic input controls where it hold measure objects i.e. Sales,Discount and margin.If we click that object it should work on cross tab.

    Thanks in advance.

  13. Paul says:

    I am trying to get an aggregate total for each day, which is base on a certain medical department. I am still new to WebI. Here is what I am looking for

    09/27/15 – Emergency Dept – 23
    09/27/15 – Outpatient – 57
    09/27/15 – Inpatient – 42

    I need all to add up for each day.. so the total for 09/27/15 would be 122, and then have this continue for each day thereafter. Any help or suggestions would be greatly appreciated. Thank you.

    • Ron Keler says:

      Hi Paul, that should be fine to do using calculation context. So you can specify =sum([count measure]) in ([day dim name]). This should give you the total across all departments per day.

  14. Karin says:

    Reposted with better formatting for data!

    Hello!

    I have a feeling For Each is my answer, but I’m having trouble figuring out the right way to use it. Please help!!

    I have data similar to below and I want to be able to compare the Start/Finish Dates of each step 1, 2 to the benchmark step 0 for each Project A, B, etc.

    Project | Date | Step
    A | 1/1/14 | Step 0 (Benchmark)
    A | 2/1/14 | Step 1
    A | 12/31/13 | Step 2
    B | 1/1/15 | Step 0 (Benchmark)
    B | 8/1/15 | Step 1
    B | 9/1/15 | Step 2

    I’m trying to confirm the dates for Steps 1 & 2 are not before Step 0.

    I tried creating a variable that pulled Date where Step = 0, but when going back and trying to use that variable, it only creates a value for those records that are Step 0. After finding this page I thought that something along this lines would be the answer, but it is still not working:

    = Date Where Step = 0 ForEach Project

    If I put this variable I’m attempting to create back in the table on the webi, I would expect to see the following data.

    Project | Date | Step | MyStep0Var
    A | 1/1/14 | Step 0 | 1/1/14
    A | 2/1/14 | Step 1 | 1/1/14
    A | 12/31/13 | Step 2 | 1/1/14
    B | 1/1/15 | Step 0 | 1/1/15
    B | 8/1/15 | Step 1 | 1/1/15
    B | 9/1/15 | Step 2 | 1/1/15

    Thanks in advance!!

    • Ron Keler says:

      I don’t think foreach is going to help here. In this scenario, i would first try to create a variable that return the 0 date for each row in the webi block, something like max([Date]) where ([Step]=0 in [Project]). Once you have a var like that, you could compare it to the Dates in steps 1 and 2…

  15. Karin says:

    Hello!

    I have a feeling For Each is my answer, but I’m having trouble figuring out the right way to use it. Please help!!

    I have data similar to below and I want to be able to compare the Start/Finish Dates of each step 1, 2 to the benchmark step 0 for each Project A, B, etc.

    Project # Start Date Step
    A 1/1/14 0 (Benchmark)
    A 2/1/14 1
    A 12/31/13 2
    B 1/1/15 0 (Benchmark)
    B 8/1/15 1
    B 9/1/15 2

    I’m trying to confirm the dates for Steps 1 & 2 are not before Step 0.

    I tried creating a variable that pulled Date where Step = 0, but when going back and trying to use that variable, it only creates a value for those records that are Step 0. After finding this page I thought that something along this lines would be the answer, but it is still not working:

    = Date Where Step = 0 ForEach Project

    If I put this variable I’m attempting to create back in the table on the webi, I would expect to see the following data.

    Project # Start Date Step MyStep0DateVar
    A 1/1/14 0 1/1/14
    A 2/1/14 1 1/1/14
    A 12/31/13 2 1/1/14
    B 1/1/15 0 1/1/15
    B 8/1/15 1 1/1/15
    B 9/1/15 2 1/1/15

    Thanks in advance!!

  16. Sam says:

    Hi All,

    Need your advice on one query!

    I Need to reflect “Lease Number” in one liner, as of now due to all the different clause type “Lease number” repetitive in report. Client is looking for lease number should be come in one row with different clause type.

    As of now Formula used :
    Clause Type : =If [Clause Type]=”Reinstatement” Then”Reinstatement” Else ” ”
    Clause Section : =If([Clause Type]=”Reinstatement”) Then([Clause Section]) Else” ”
    Clause Text : =If([Clause Type]=”Reinstatement”) Then([Clause Text]) Else” ”

    Note : Same logic used in all the diffrent caluse type.

    I am using BO XI 3.1.

    Lease Number Clause Type = R Obli Clause Type = Surr Clause Type = Tenant
    1000622 Tenant
    1000622 Surr
    1000622 R Obli

    Note: If you haue any confusion then pls go thorough this link: http://scn.sap.com/message/15710123#15710123

    Thanks
    Sam K

  17. Brenda says:

    Hi,

    I’m new to SAP BO and I’m having a hard time getting the correct results in a summary report (and I don’t have access to the Universe, for changes into that I need to request them to somebody else).

    I need to get the date of a maximum amount,and only get one row per person (there may be more than one expense per date).

    So from this:
    Person DateExpense Amount
    1 01/01/2015 $5
    1 01/02/2015 $5
    1 01/03/2015 $25
    1 01/03/2015 $20

    I need to get this:
    Person DateExpense Amount
    1 01/03/2015 $45

    and I’m having a hard time getting the correct value, when I try to create a variable like this:

    MaxAmountDate = [ExpenseDate] Where ([Amount] In ([Person];[ExpenseDate]) = [MaximumAmount])

    or

    MaxAmountDate = [ExpenseDate] Where ([Amount] = [MaximumAmount])

    When I test it, I get rid of the ExpenseAmount and I add a column with the variable name and it seems to compare the sum of all the amounts of all the report against the maximum value and I get nothing.

    I have tried with an “IF” as well:

    MaxAmountDate = If ([Amount] In ([Person];[ExpenseDate]) = [MaximumAmount]) Then [ExpenseDate]

    Now I could get the date of the maximum amount in all rows but I still get multivariable when getting rid of the DateExpense row so that I get a summary row, which is what I need, even if I apply the Max function (alone or combined with the In()):

    I also tried Code:
    = If (IsNull(Max([ExpenseDate] Where (Max(Amount] In ([Person];[ExpenseDate])) In ([Person];[ExpenseDate]) = ([Largest Gift Amount]) In Report) In ([Person])));””;Max([ExpenseDate] Where (Max(Amount] In ([Person];[ExpenseDate])) In ([Person];[ExpenseDate]) = ([Largest Gift Amount]) In Report) In ([Person])))

    And thinking that maybe the Max wasn’t working because of the date format, I also tried:

    = Max(ToDate(If (IsNull(Max([ExpenseDate] Where (Max(Amount In ([Person];[ExpenseDate])) In ([Person];[ExpenseDate]) = ([Largest Gift Amount]) In Report) In ([Person])));”01/01/1901″;Max([ExpenseDate] Where (Max(Amount In ([Person];[ExpenseDate])) In ([Person];[ExpenseDate]) = ([Largest Gift Amount]) In Report) In ([Person])));”MM/dd/yyyy”))

    But it didn’t work either.
    When I include the ExpenseDate field, the table shows as if it’s getting the right “date of maximum amount” and the other rows are left with blank, but once I get rid of the ExpenseDate column in order to be left with only one row, I get the MultiValue. And I can’t just add a filter to the report table because the this is part of a bigger report, so several other columns are involved (although this is the only one giving me a hard time).

    How can I get this date of the maximum amount into only one row for this case?

    Your help will be greatly appreciated,

    Brenda

  18. Ron Keler says:

    I recently worked with a colleague on a related problem and we used the last() and first() function available in 4.0 to solve similar issues.. Please be sure to check those functions and if you are looking for ways to get a first or last value from a multi-dimensional dataset

  19. Salvador says:

    Hi Ron,

    My issue is having the next example, I want to have a total that is neither the sum nor average, instead the result has to be determined by a KF01 if it is on “1” and aggregated by the count of different materials. Actually I am interested on the Total…

    Plant Customer Material KF01
    3322 01 123 1
    3322 01 987 1
    3322 02 123 1
    3322 03 123 0
    3322 03 456 1
    TOTAL 3

    If I take out the Material Dimension, it should look like:

    Plant Customer KF01
    3322 01 2
    3322 02 1
    3322 03 1
    TOTAL 3

    Result = 3 because there are still the same, not repeated materials, and the count is still at material level.

    Can I use the ForEach() in here or what do you suggest ? I have already used the count() and aggregate() functions but not working.

    • Salvador says:

      I already did it on the BEx Query Designer with exception Aggregation (count) and Material as reference Characteristic, but the total is displayed as #notavailable even if I refresh the webi. So I want to replicate it on webi creating a variable on it.

      WEBI 4.1
      BEx 7.x

    • Ron Keler says:

      Have you tried summing the counts?

  20. Roshan says:

    Hi Ron,

    After spending hours browsing through websites looking for a solution I finally found this. Thanks to you I was able to solve the #MULTIVALUE error and keep the little hair I have left after this ordeal 🙂

    Thanks again!!!

  21. Kevin says:

    I would like to choose the first of surgeon cases in each of the 23 operating rooms and if a surgeon is schedule to operate in the first case in multiple rooms, I only want his earliest case. I am using the Min function =if(Min[scheduled startdate and time] In ([Actual OR Number])=(Scheduled Start Date and Time] And (First Scheduled for each surgeon]=Scheduled Start Date and Time]);1). I also have a variable =Min([scheduled Start Date and Time]) in ([scheduled Primary surgeon]). My results on the report show the same surgeon in Multiple OR rooms, I only want the surgeons earliest case. Any suggestions will be appreciated.

  22. Kven says:

    Hi,
    I have similar kind of issue.
    I have created a dimension and using “ReportFilter” function, i am fetching the result from query object. The result look like
    A;B;C;D…

    But, my requirement is to get result in,
    A
    B
    C
    D

    Is there any function which can convert the result from one single row into columns?

    Your help is greatly appreciated.

    thanks,
    Kven

    • Ron Keler says:

      Hi Kven, I suggest you push this back to the database level, oracle for example has string aggregation functions like listagg that will do something like this…

  23. Gayathri says:

    I am running into a kind of similar issue. I have a report with 2 similar queries for different businesses with 3 tabs for Business A, Business B and Business A+B. Each of the tabs need to display Top N dimensions (along with the rank), their attributes and the measure value. One of the attributes is derived from another table. It changes over time and we only need the latest attribute but the total value for the measure (I can achieve this with a calculation context). The rank still works. But I have 2 lines for each of the attributes even with a calculation context for the measure.

    Current scenario:

    Rank Dim Attribute1 Attribute 2 Attribute 3 Measure
    1 X ABC 2000
    2 Y ABC 100
    2 Y DEF 1000
    3 Z ABC 50
    3 Z DEF 1000

    What I need: Assuming DEF is the latest attribute based on a date

    Rank Dim Attribute1 Attribute 2 Attribute 3 Measure
    1 X ABC 2000
    2 Y DEF 1100
    3 Z DEF 1050

    Your suggestion is most welcome and appreciated.

    Thank you!

    • Gayathri says:

      Oops! The values are not aligned under the right header. Please match the numbers to the measure, ABC/DEF to Attribute 3 and X/Y/Z to Dimension. Let me know if you have any questions.
      Thanks!

      • Ron Keler says:

        Gayathri, i think your issue is related to the ranking, so you need to rank on the right combination of dims. Also, look for info on dense rank in webi, there are many posts about this topic with nice work around related to generating random numbers to make the ranking dense.

  24. Kiruthika says:

    Hi I have a similar situation. But FOREACH does not seem to solve my issue.

    Here is my scenario.
    Date Count of orders
    1/1/14 – 1
    1//2/14 – 1
    1//2/14 – 1
    1//2/14 – 1
    1//2/14 – 1
    1//3/14 – 1
    1//3/14 – 1

    The data is displayed as above.

    But I want it to be displayed as below.
    1//1/14 – 1
    1//2/14 – 4
    1//3/14 – 2

    Any help is greatly appreciated.

    • Ron Keler says:

      Hi Kiruthika, this does not seem to be a scenario that requires any special calculation context formulas, a simple count([order number]) should do the trick. What i suspect might be happening is that your dates have time stamps in them, so while their format only display the date portion on the report block, the actual value includes the time, so webi is not aggregating by day. Try to use a formula to convert the date to a character type in the format you need (like m/d/yy as you have in your example)

  25. Sumit Pillai says:

    Hi Ron, great article!! I was working on a similar issue, hope you will be able to give me a solution. I have a dimension object – [customer]
    and a measure object –
    [sales]
    I created a variable [top customers] as a dimension having formula –
    =If([sales]>100000;[customer];”others”)

    Now I pull the variable alone in the report and expect to see result like this –
    customer1
    customer2
    customer3
    others

    But I’m getting #MULTIVALUE error.

    I tried the ForEach approach but still the error comes.

    Not sure how to resolve this.

    • Ron Keler says:

      This should work Sumit. I assume you need to create a block with two columns, customer and sales. Make sure to convert the formula to a variable of type dimension for the customer, and sum() the [sales} measure in the next column.

    • vikash says:

      Use the variable qualification as dimension

  26. teja says:

    hi,
    this is teja i have a requirement like

    cities–>column below values
    Kolk
    Noid
    gurgaon
    chennai

    i want to display the in place of Kolk,Noid,gurgaon–>NCR HOw to reslove this

    • Ron Keler says:

      Hi Teja, i’m not 100% sure i understand the question, but if what you are looking for is replacing the values Kolk,Noid,gurgaon with the single value NCR, you can simply create a variable in the report and use an if statement to say if cities = Kolk or Noid,gurgaon then NCR. You can look up the webi syntax in the help files. If you would like to do this for more then one report, it would be better to create a universe object with a case statement to accomplish this. Thanks.

  27. Jay C says:

    I have a situation somwhat similar to above. I have a report like below:

    Id DP
    1098 FIN
    1098 DRM
    1098 CES
    1098 CTS
    I would like to create a variable that replaces the four rows of DP and put the four values in a comma delimited text resulting in one row of data. Is this possible?

    Thanks
    Jay

    • Ron Keler says:

      Hi Jay, you can use webi formulas to accomplish this, but the solution you can accomplish in webi will probably be limited. I think this will be better solved on the data side. For example, if you are using oracle as your backend, you can use the listagg function to “convert” your rows to a delimited list, either in the universe or further down stream..

  28. Christos says:

    In your final result above, suppose you wanted another column, BestYear, listing the state’s best year. e.g., suppose Illinois had its best year in 2002 so you’d want “2002” to appear in the new column on the row with “Illinois” in it.

    I would assume you’d do this:
    BestYear = [Year] Where ([Revenue]=(Max([Revenue]) In ([State])))

    It seems that adding a column for
    BestRevenue = Max([Revenue]) In ([State]))
    works just fine, but the context doesn’t work within the Where clause in the BestYear calculation above – it instead returns #MULTIVALUE errors.

    • Ron Keler says:

      Christos, your calc looks fine as “pseudo code”, hard to say where the multivalue error is coming from. In the specific example i published, the first year column might be causing this. My suggestion in these situations is always to start simple and gradually increase the complexity level. So begin by creating a block with states and max revenue. Then, try to add another column where you can compare revenue to the calc from column 2. Also, you might be able to accomplish this with ranking, could be simpler solution.

  29. Louise Just says:

    Hi Ron,
    I like your article, is comes very close to addressing a #MULTIVALUE issue I am experiencing. I am having trouble getting my context correct in my formula and thought you might be able to help.

    I have a cross tab with product groups down the left column and date periods across the top (eg TY MTD, LY MTD etc). I have a total column for each Date set eg total column for MTD, YTD, MAT etc. I have a variable that provides +/- variance. The variable is ([TYMTD$s]-[LYMTD$s])/[LYMTD$s]. As I have MTD, YTD, MAT in the one table, I have created an If statement variable that shows the correct calculation variance in the correct total column eg =If [Date Period]=”MTD” Then [MTD $s +/- Total] Else If [Date Period]=”YTD” Then [YTD $s +/- Total] Else If [Date Period]=”QTR” Then [MQT $s +/- Total] Else If [Date Period]=”MAT” Then [MAT $s +/- Total].

    This works fine for each of the rows in the table, however my issue is with the total row, where I get #MULTIVALUE. For the total row in other reports I have built I have used an aggregation function like sum, min, max etc which have worked, however, in this case none of these are suitable.

    I am happy to email you the report to assist with understanding my calculations.

    Your thoughts on how I could achieve my desired result? Thank you in anticipation of your assistance.

    Regards
    Louise

    • Ron Keler says:

      Hi Louise, i think you are on the right track with the aggregate function, but there might be some additional complexity in your particular example. Another approach you might be able to use is multiple queries (data providers) for the different date cuts. As long as you can merge the date dim (and any other dim you use in the block), this could simplify your variables a lot. Assuming your query time is short.

Comments are closed.