Market share trend chart

[ahammadshafi]

Hi:

I need to calculate market share for a trend chart.

The conditions are as follows:

1. The user will be able to select:

    a. Year

    b. Month

      from two list boxes.

2. Based on the user selection market share for last 13 months will be populated.

At first place I tried the following expression:

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} CValue)

/

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} total CValue)

But it was summing denominator for all the 13 time periods.

Then I tried the following expression:

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} CValue)

/

Sum(total <PeriodDate> CValue)

In this case the denominator is for the latest period.

Looking forward.

Shafi

[#2]

i did not understand the requirement. If user selects year say 2013, then market share for last 12 months of 2103  will be populated or what?

[ahammadshafi]

Hi Khusboo:

Please note, if the user selects 2013 and May then the trend chart will show market share for "May 2012", "June 2012", "July 2012".... to "May 2013".

Shafi

[stigchel]

The market share of what, not months I'm guessing. If it is e.g. a product an you have one selected I would say

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} CValue)

/

Sum({<Product=,year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>}  CValue)

[ahammadshafi]

Hi Piet:

Sorry, it did not work. After applying the formula it is showing MS=100% for all the products.

The situation is like that the user will not select a single product. It will always show market share for all the products for last 13 months.

The user will just change the Year and Month from two list boxes.

Shafi

[ahammadshafi]

Hi Piet:

Along with the given information please note, in the data the Products are not at the granular level; below Product there is another hierarchy called ProductVariation and hence the sales of the product over time is calculated like:

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} CValue).

And I thought total market value should be calculated like:

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} total CValue).

Please also note there are two dimensions in the data:

1. Market: Product -> ProductVariation

2. Period: Year -> Month -> PeriodDate

In the fact table there is only one measure:

1. CValue = Sales value for current month

Shafi

[stigchel]

If Product is your second dimension in the chart, then your first suggestion should work

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} CValue)

/

Sum({<year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} TOTAL CValue)

Or do you use multiple Expressions?? In that case use something like e.g for product DIANE

Sum({<Product={'DIANE'},year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>} CValue)

/

Sum({<Product=,year=,PeriodDate={">=$(=AddMonths(Max(PeriodDate),-12)) <=$(=Max(PeriodDate))"}>}  CValue)

Otherwise please share a sample qvw demonstrating the issue

[ahammadshafi]

Hi Piet:

Sorry for the delay.

Please find the attached qvw file for your reference with two required charts.

Looking forward.

Shafi

[stigchel]

I think there is something wrong with your data and moving annual total setup. If I remove the set expression for the market share, then the results are the nr of periods, e.g for 2015 3 times, lower as they should be.Select one period and the results are three times higher, this should not happen Please read through e.g. the following thread how to handle rolling periods, or search for them among the many threads there are about this.

If you then still have any questions regarding this, then I suggest you open up a new thread by posting a new question

Calculating rolling n-period totals, averages or other aggregations

[ahammadshafi]

Many thanks Piet.

The link has made many things clear. If necessary, I will be creating another thread with more  specific questions for this.

Shafi