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.
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.
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)
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
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…
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!
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..
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
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..
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
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?
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])
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.
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.
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
You are close Jeff, try this: =Average(If(DayName([Query 2].[Invoice Date]) = “Monday”;[Query 2].[Count of Orders];0))
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
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…
This formula is exactly what I needed to filter some subtotals on a crosstab, thank you very much!
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.
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.
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.
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!!
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…
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!!
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
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
Hi Brenda, this should work:
Column1: =[Person]
Column2: =max([Date]) in ([Person])
Column3: =sum([Expense Amount]) in ([Person])
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
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.
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
Have you tried summing the counts?
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!!!
Thanks for the kind words 🙂
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.
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
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…
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!
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!
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.
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.
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)
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.
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.
Use the variable qualification as dimension
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
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.
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
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..
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.
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.
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
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.