Cannot build vertical stacked bar chart! HELP!

[Former Member]

I have a simple stacked bar chart I want to build with month/year across the bottom that shows the number of orders by category (A,B,C and D). I understand that I must have 2 dimensions and 1 measure. However, the chart is not counting the number of orders by category correctly. I cannot find anything on the net about how to create a vertical chart and troubleshooting why it won't work.

Here's an Excel Pivot of the data:

Year/tmonth A B C D

2009/12 1 1

2010/02 1 1 2

2010/04 1 1

2010/06 1 1

2010/07 2 1 3

Grand Total 2 1 4 1 8

[Former Member]

Hi Amy,

 I understand that I must have 2 dimensions and 1 measure. 

There is no need to have 2 dimesnsions for the vertical stacked bar chart. The 2nd dimension is optional. Also, you need more

than 1 measure to have stacks on the chart else with 1 measure it would be normal bar chart.

Now, as per your requirement, I suppose that you have 5 objects that needs to be plotted on the chart. (Ignoring the Total part)

1 date (dimen) on "X-axis" 4 Order Count by Category on "Y-Axis"

If you do not have 4 measures ( 1 measure per categ) then please create it, or else check the formula that you used to

build it. Eg The defn. should be something like this:

 v Ord Count A = (Count(Orders) Where (Category="A")) 

similarly you can create other 3 variables.

I hope this would solve your problem. If not then can you give me more details of your data, definition of variables that you

tried and the issue which you are facing.

Regards,

Rohit

[Former Member]

Ok - I'll try to work with your response and get back with you - Thank you very much for taking the time to explain. WEBI seems so difficult compared to MS Access.

[Former Member]

I'm unable to use SQL as it's greyed out in our program. We use OLAP and the technical guys tell me that the SQL wouldn't work with our system anyway. Is there a way to create these variables using the formula option in WEBI?

[Former Member]

Hi Amy,

You don't need to use the SQL option for creating these variables.

The variables needs to be created on the report level i.e. in the web-i only after the query has been refreshed.

Regards,

Rohit

[Former Member]

I am absolutely going to cry. I cannot get the variables to work. It may be an issue of using data from 2 universes, I really don't know. I'm new to WEBI and cannot get this to work no matter what I try to do. I created 4 measures for my cat as follows:

var_stackA - =If([CAT] ="A";1;0)

var_stackB - =If([CAT] ="B";1;0)

var_stackC - =If([CAT] ="C";1;0)

var_stackD - =If([CAT] ="C";1;0)

The only dimension I'm using is the month period as follows:

var_chart_receiptmonth - =FormatDate(ToDate([var_corereceiptdate];"mm/dd/yy");"yyyy/mm")

I'm first viewing the results in the vertical table view to make sure all the data is showing. Once I remove the notification number (which is the primary key), the table groups by the receipt month and only shows 1 row for the measures. In the case below, it removes all the var_stackA's:

BEFORE:

Notification var_chart_receiptmonth var_stackA var_stackB var_stackC var_stackD

12344444 2010/07 0 1 0 0

22245600 2010/07 1 0 0 0

63355787 2010/07 1 0 0 0

43387877 2010/06 0 0 1 0

AFTER:

var_chart_receiptmonth var_stackA var_stackB var_stackC var_stackD

2010/07 0 1 0 0

2010/06 0 0 1 0

In order for the stacked bar chart to work right, I need it to recognize the A (var_stackA). I've tried to change the properties of the table to avoid duplicate row aggregation; however, that messes up my calculations - probably due to the merge of the universes somehow. You can see how every row for var_stackD has been coded as 1:

AFTER CHECKING THE AVOID DUPLICATE ROW AGGREGATION:

Notification var_chart_receiptmonth var_stackA var_stackB var_stackC var_stackD

12344444 0 0 0 1

22245600 0 0 0 1

63355787 0 0 0 1

43387877 0 0 0 1

[Former Member]

Hi Amy,

First of all "Avoid Duplicate Row Aggregation" check box should be unchecked as duplicate rows must be aggregated (here

month will represent the duplicate row). And with the help of formula you should be able to get the below data to plot the chart:

Rcpt Month var_stackA var_stackB var_stackC var_stackD

2010/07 2 1 0 0

2010/07 0 0 1 0

Note:Considering the data in the above post: Please let me know if my understanding is corrrect.

The variable defn. you have mentioned var_stackA - =If(CAT ="A";1;0) is simple if condition. I think it should be:

 var_stackA - = Sum(If(CAT ="A";1;0)) 

to get the above required result.

I have one doubt - In the very first post you have mentioned that you want to count the orders acc. to the category. so its

better to go with the formula I have mentioned in my earlier post.

If still you are not able to make the chart then Can you give the details of objects in the two data providers and the dimension on which you are merging them..?

Regards,

Rohit

[Former Member]

OK, I've used your formula in my measure variables as follows:

var_stackA =Sum(Count([CAT])Where([CAT]="A"))

var_stackB =Sum(Count([CAT])Where([CAT]="B"))

var_stackC =Sum(Count([CAT])Where([CAT]="C"))

var_stackD =Sum(Count([CAT])Where([CAT]="D"))

This is my result (as long as I leave the notification in the table):

Notification var_chart_receiptmonth CAT var_stackA var_stackB var_stackC var_stackD

200340495 2009/12 D 1

200364557 2010/02 B 1

200366789 2010/02 C 1

200372262 2010/04 C 1

200427391 2010/06 C 1

200430078 2010/07 C 1

200433242 2010/07 A 1

200434096 2010/07 A 1

This is my result (when I take the notification out):

var_chart_receiptmonth CAT var_stackA var_stackB var_stackC var_stackD

2009/12 D 1

2010/02 B 1

2010/04 C 1

2010/06 C 1

2010/07 C 1

Where did the A category counts go??

I've got 2 universes: GR_MRO Notification Tasks and GR_MRO Operations Details

I'm merging on two dimension variables (notification and sales organization)

My report data is filtered using the following 2 filters:

var_A = 1 calculated via =If([Task Code]="2/CT-HOLD/RTQT";1;0) - detail variable

var_RTQTDate > 1 calculated via =If([Task Code]="2/CT-HOLD/RTQT";Min([Created On]);0) - measure variable

Task Code is a dimension variable from the GR_MRO Notification Tasks universe

A few more calcuations for your review:

CAT = If([var_DaysAged2]<6;"A";If([var_DaysAged2]<11;"B";If([var_DaysAged2]<21;"C";"D"))) - dimension variable

var_Days Aged2 =If(IsError(DaysBetween(Min([var_corereceiptdate]);Min([var_RTQTDate2])));0;DaysBetween(Min([var_corereceiptdate]);Min([var_RTQTDate2]))) - measure variable

var_corereceiptdate =[Receipt Date] - detail variable

[Receipt Date] - variable from the GR_MRO Operation Details - dimension variable

Edited by: Amy Jones on Jul 29, 2010 11:18 AM

[Former Member]

As far as I can see, you have a column for each category.

Open a report and create a cross tab; with time in rows, ONE category in columns. Now, drag and drop the chart over the table. You may have to change the organizations of your data...

Christian

[Former Member]

Unfortunately, I can't get it to work no matter what I do. This is simple when bringing the data in Excel, creating a pivot and then the stacked bar. Why is WEBI so difficult??

Is there a way I can send you the report so you can help me build the chart?