Days to be displayed in range 1

[karkia]

Hi,
I need to design a report, in WebI XI r1, for "The qty of product that have been in stock for some range of days (say, since they were brought to the warehouse till today). I have Product dimension, #ofDays in inventory and the qty. Looks simple. But the problem I am facing is that the report should display the #ofDays as a range-

#ofDays Product1 Product2
------- -------- --------
1-30 10 5
31-60 8 5
61-90 4 2
>90 1 0

The # of Days is passed from the universe as some integer value. How do I summarize them into ranges, as above?

 

[blom0344]

you can either use a grouping variable or use a complex 'if-then-else' construction within the report.

However, if this is a returning requirement, it could be solved very handsomely by a universe object by use of CASE/DECODE construction

Ties Blom
Information analyst

 

[karkia]

Hey Blom,
THanks for the advice. But I would really appreciate if you would elaborate on each of the methods that you mentioned.

 

[blom0344]

= If (<Orderline> >1 And <Orderline> <= 10) Then "1-10" Else If(<Orderline> >10 And <Orderline> <= 100) Then "11-100" Else ">100"

Example splitting integer values into 3 groups..

Ties Blom
Information analyst

 

[karkia]

Thanks Blom, but how would it summarize the corresponding measures (here, product_qty) for these new ranges? For example, there might be 3 product1's with 5 days in inventory;10 product1's with 20 days. Now, I should have-
#ofDays Product1
------- --------
1-30 (3+10=) 13 how to sum them for the range?

 

[blom0344]

Uhh,

Assuming BO has not changed it's rollup characteristics, measures should automatically roll up over the dimension they are displayed with.

The new grouping variable is a new dimension of course.

But I have not seen XI yet, something could have changed ...(hope not)

Ties Blom
Information analyst

 

[karkia]

Works great. Thanks Blom.

 

[karkia]

Hey Blom, now don't shout at me. But, how would this work in case of non-numerics. For example, if I have-
#ofDays ProductName
------- --------
5 P1
9 P2
25 P1
30 P2
50 P2
55 P3
80 P1
How do I count the ProductName for the range of days?
Below is what I need-
Days P1 P2 P3
---- ---- --- ---
>60 1 0 0
31-60 0 1 1
1-30 2 2 0
When I try COUNT(ProductName), it just returns one record, and the days range appears as multivalue.

 

[blom0344]

karkia,

Create crosstab like:

Productname
-----------------------
Days | Count(#ofDays)

Anyway, that is what I need to do with full-client..

P.S. I never meant to 'shout' in any case..



Ties Blom
Information analyst

 

[karkia]

Cool. Thanks Blom.

 

[karkia]

As per the suggestions,I have created cross-tab report and sectionized it to reporesent for CurrentMonthEnd and PreviousMonthEnd, like below-

CurrentMonthEnd
Productname
-----------------------
Days | Count(#ofDays)

PreviousMonthEnd
Productname
-----------------------
Days | Count(#ofDays)

Now, is it possible to get the difference between the Counts for the two sections?

 

[blom0344]

Well, can't test this for you , cause I'm not at work right now, but try a new variable in the body of the crosstab like:

=(Count(#ofDays) Where ...... = CurrentmonthEnd) -
(Count(#ofDays) Where ...... = PreviousmonthEnd)

In any case, this should be done with the sections that you mention disabled (other it would restrict the calculation)

I don't know exactly how your variables are named, but I imagine there is one that stores the values for CurrentMonthEnd / PreviousMonthEnd?

Ties Blom
Information analyst

 

[karkia]

I had that in mind, but how do I use the Where clause? I work with BO Webi XI rel 1. The currentmonthend and PreviousMonthEnd are predefined queries in the universe. I guess I can make use of other dimensions to get the equivalent for use in the formula.

 

[blom0344]

karkia,

I think you're the kind of developer that is looking for full-client functionality.
I am appalled that this stuff is not covered by Webi XI..

Ties Blom
Information analyst