Rolling Four Quarters in Webi

[Former Member]

Hi Friends,

I have a requirement explained below.

I have Country and state in Vertical axis and brand family in horizontal axis as shown in image.

I have four measures in body axis.

I am getting volume from 2 data providers for current year and last year. i have to get difference of the volume for each quarter.

Ex: 2013Q1-2012Q1 in first cell of body axis.

and 2013Q2-2012Q2 in second cell of body axis.

In the same way for all the quarters i have to get according to Country(Not classified at this level),state(Not classified at this level) and Brand Family(24/7).

Thanks,

Kumar

[Former Member · Accepted solution]

Hi Friends,

I have worked out solution for this.

Here are the steps to follow.

1. I have taken substring of Quarter and Year from the Sales Cycle Separately. i have data like 2013Q1 in Quarter attribute

Quarter=Substr([MSAi Sales Cycle];5;2)--- In this variable i will be having Q1,Q2,Q3 and Q4.

Year=Substr([MSAi Sales Cycle];1;4)--- In this Variable i will be having 2013 and 2012.

I had 2 data providers for current year and last year.

2. For numerator i have created a variable Volume.

Volume=([Current Year].[Sales]) In ([Quarter])-([Previous Year].[Sales]) In ([Quarter])-- i will be getting the difference of Current quarter of this year(i.e 2013Q2) and Same quarter of Last year(i.e 2012Q2).

In this way we will be getting for rest of the quarters.

For the percentage %

3.For Denominator

Chg % LY=[Volume]/([Previous Year].[Sales] In ([Quarter]))

Hopefully this will be helpful to someone.

Regards,

Kumar

[Former Member]

Hi,

Its difficult to understand and help , please provide some more info:

Measure names, how are they places in the Crosstab , what is expected result and where in the crosstab ?

As we know inserting a column in Crosstab , inserts column in every interval of the column.

Thanks,

Avinash

[Former Member]

Hi Avinash,

Thanks for your reply.

i have the Volume for current year(2013) and last year(2012).

As you can see in cross tab i have four Body cells right in each cell i have to place the value for

1) 2013Q1-2012Q1-- First measure

2)2013Q2-2012Q2 -- Second Measure

like wise for Q3 and Q4.

Hope Now you got it..

Thanks,

Anand

[Former Member]

Hi Avinash,

It is a cross tab with Country and state in a vertical axis and brand family in horizontal axis.

Measure names:

Q1=2013Q1-2012Q1

Q2=2013Q2-2012Q2

Q3=2013Q3-2012Q3

Q4=2013Q4-2012Q4

These four measures have to fit into those body Axis of cross tab.

Thanks,

Kumar