Not able to calculate Market Share

[#1]

Hi All, I am trying to calculate Market share with the below expression = if ( SubMetric = 'Volume', sum(Volume), if( SubMetric = 'Market Share', (round(sum(Volume)) / round(sum({$}Volume))))) But it is giving me 100%. Please suggest. Regards, Manish Virmani

[jonathandienst]

= if ( SubMetric = 'Volume', sum(Volume),

  if( SubMetric = 'Market Share',

  (round(sum(Volume)) / round(sum({$<Brand = >} TOTAL Volume)))))

[#3]

Hi Jonathan, Thank you for your response. By this it is giving the sum of total volume ignoring the Week. As i am showing the week on X axis so we need to have the week wise value. Regards, Manish Virmani

[jonathandienst · Accepted solution]

Then play with qualifiers on the total:

... TOTAL <Brand> Volume)

... TOTAL <Week> Volume)

to get the results you require.

[Digvijay_Singh]

Try TOTAL <week> kind of to define the dimension of Total.

[#6]

Hi Digvijay, Please explain a bit more. I have very little knowledge of qlikview. Regards, manish virmani

[jagan]

Hi,

Try this expression

= if ( SubMetric = 'Volume', sum(Volume),

  if( SubMetric = 'Market Share',

  (round(sum(Volume)) / round(sum({$<Brand = >} TOTAL <period>Volume)))))

Note: TOTAL <period> will ignore the Brand dimension and get the total by period.

Hope it helps you.

Regards,

Jagan.

[#8]

Thanks you Jagan, Jonathan and Digvijay. Regards, manish virmani

[#9]

Hi Jagan, In my actual data set i have columns like Periodicity (have value like Weekly/ Monthly) and period (have value like Week1,Week2.. / Month1,Month2..) When in Periodicity Weekly is selected value in 'period' will show Week1,Week2.. when in Periodicity Monthly is selected value in 'period' will show Month1, Month2.. But using the expression gives all the values where period is month also (round(sum(Volume)) / round(sum({$} TOTAL Volume))))) Regards, Manish Virmani

[jagan]

Hi Manish,

There is no Monthly value in Period filter, also you are not using TOTAL <period> in the expression.  Can you update the file and let us know your expected output for the selections then it would be easier to give the solution.

Regards,

Jagan.