topic Re: Market share trend chart in QlikView

Market share trend chart

ahammadshafi — Fri, 15 May 2015 11:47:41 GMT

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

Re: Market share trend chart

Fri, 15 May 2015 12:24:44 GMT

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?

Re: Market share trend chart

ahammadshafi — Fri, 15 May 2015 12:30:42 GMT

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

Re: Market share trend chart

stigchel — Fri, 15 May 2015 12:39:03 GMT

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)

Re: Market share trend chart

ahammadshafi — Mon, 18 May 2015 03:37:14 GMT

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

Re: Market share trend chart

ahammadshafi — Mon, 18 May 2015 05:05:41 GMT

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

Re: Market share trend chart

stigchel — Mon, 18 May 2015 09:18:24 GMT

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

Re: Market share trend chart

ahammadshafi — Wed, 20 May 2015 08:29:57 GMT

Hi Piet:

Sorry for the delay.

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

Looking forward.

Shafi

Re: Market share trend chart

stigchel — Wed, 20 May 2015 12:42:09 GMT

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

Re: Market share trend chart

ahammadshafi — Thu, 21 May 2015 03:35:27 GMT

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

Re: Market share trend chart

qlikviewwizard — Thu, 21 May 2015 08:20:55 GMT

Hi ahammadshafi,

If you got the things, Please close the thread by selecting Mark As Answered.

Re: Market share trend chart

ahammadshafi — Mon, 25 May 2015 04:34:28 GMT

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