Skip to Content
author's profile photo Former Member
Former Member

Creating query depending dimension 's LOV

Post Author: Ferhat

CA Forum: WebIntelligence Reporting


i 've got a problem with creating a report like that:

i ve got a year and month dimension objects and sales measure object. İn year object i've got 1998, 1999 and 2000 datas.

i want to do add column that doing this:

for 1999( for april : ( (sales in 1999 april - sales in 1998 april) / (sales in 1998 april))

for may : ( (sales in 1999 may - sales in 1998 mayl) / (sales in 1998 may)) ...)


for 2000( for april : ( (sales in 2000 april - sales in 1999 april) / (sales in 1999 april))

for may : ( (sales in 2000 may - sales in 1999 mayl) / (sales in 1999 may)) ...)

how can i do this in web intelligence..i need help

tanks a lot...

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Sep 08, 2007 at 05:16 PM

    Post Author: James MacDonald

    CA Forum: WebIntelligence Reporting

    You should be able to do this using Webi's report variables.

    For April 1999 Sales

    v_Sum1=Sum(if([Year]=1999 and [Month]="April";[Sales];0))

    v_Sum2=Sum(if([Year]=1998 and [Month]="April";[Sales];0))

    v_Ratio=Sum(if([v_Sum2] = 0;0;([v_Sum1] - [v_Sum2]) / v_Sum2))


    You can prompt for the year and month and then test the prompt results instead of the hard coded values. It is important to use the 'Sum()' function as the variable is recalculated on each row and would probably be 'Multivalue' without the sum.

    I didn't test this so I can't guarantee the syntax is right but it should be close.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.