Percentage caluculation of sales using the from and to qtr prompts from bex query.

[former_member210043]

Hi Experts  ,

we are getting the data to the Webi report  from  bex query where we are to mandatory Prompts as Financial Qtr (from) and Financial Qtr(To) for the financial Qtr object.

To calculate  the % of sales quarter wise we are  using the formula  as (sales of respective Qtr -Previous Qtr Sales) /Previous Qtr sales.

So to get the Previous Qtr sales we asked BI team when ever user selects the from Qtr prompt  provide the sales values  from the previous Qtr.

ex: From : 2010.3 to 2011.1

then the bex query will provide the sales as

financial Qtr     Sales

2010.2                  100

2010.3                  150

2010.4                   200

2011.1                   250.

with this I am able to calculate the % sales but for one cross table  in the same report

I need to display the sales as qtr wise

So when I Drag n Drop the Financial Qtr, Sales

it wil display the values as

financial Qtr   sales

2010.2              100

2010.3               150

2010.4               200

2011.1                250.

So How to avoid displaying of the previous qtr value of the financial Qtr Dimention for this Table

Please Help Me ....  Urgent.....

Thanks

Regards

Nikil

[former_member189638 · Accepted solution]

Create a variable v_flag in the report with the below formula.

=If([financial Qtr ]=Min([financial Qtr ])  ForAll ([financial Qtr ])) Then "Y" Else "N"

Then apply a filter to the table with the condition v_flag =N

[former_member210043 · Accepted solution]

Hi Rakesh  ,

Thanks a lot........., U response is working  for Financial Qtr. Object .

but I have used the Financial Qtr  object to calculate the Quarter.Year variable format As Q1.2010 if the value is  20101

with Formula  ="Q"+Right([Financial Year Quarter];1)+". "+Left([Financial Year Quarter];4)

And I used the financial Qtr n Quarter.Year as  Horizontal dimetions and I  hide the Financial Qtr  in Cross Table.

Ex:

financial Qtr   Quarter.Year

20101                Q1.2010

20102                Q2.2010

Then I am not able to see data including headers ,

if I remove the Quarter.Year dimention  then it is working fine ... 😞

Please help me .....

Thanks

Nikil

[former_member189638 · Accepted solution]

What are the other objects that you are using in the Cross Tab? Couple of screenshots will help in understanding the issue as well.

[former_member210043 · Accepted solution]

Hi Rakesh  ,

Thanks for the response , I have resolved the problem by creating the one more variable

Thanks

Regards,

Nikil..

[former_member210043 · Accepted solution]

Hi Rakesh ,

I am having the problem in %percentage calculation , Plz check the above image  help me .....

Thanks

Nikil