Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ahammadshafi
Contributor

Market share trend chart

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

1 Solution

Accepted Solutions
ahammadshafi
Contributor

Re: Market share trend chart

Hi Everyone:

Many thanks for your kind help.

We could manage to come up with the correct expression:

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

/

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


Shafi

View solution in original post

11 Replies
Not applicable

Re: Market share trend chart

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
Contributor

Re: Market share trend chart

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

Partner
Partner

Re: Market share trend chart

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
Contributor

Re: Market share trend chart

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
Contributor

Re: Market share trend chart

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

Partner
Partner

Re: Market share trend chart

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
Contributor

Re: Market share trend chart

Hi Piet:

Sorry for the delay.

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

Looking forward.

Shafi

Partner
Partner

Re: Market share trend chart

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
Contributor

Re: Market share trend chart

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