topic Re: Want to calculate market share based on the partial sums in QlikView

Want to calculate market share based on the partial sums(second dimension percentages)

kkkumar82 — Tue, 11 Nov 2014 07:41:09 GMT

I have a pivot table where I have an expression market share which is calculated by the following expression

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Sum( total{1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Here the problem is I want the denominator value as the partial sum of that particular dimension value but because I have taken

total qualifier it is taking the sum of all dimension values.

for eg , for commodity ABC,Supplier XXP the spend is 82,916, the market share should be 53.177(82916.90/155924.04)

but I am getting 2.44 because I have used total qualifier it is taking the whole commodities spend.

How to get that partial sum of commodity in the expression.

Re: Want to calculate market share based on the partial sums

MK_QSL — Tue, 11 Nov 2014 07:44:51 GMT

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Sum( total <Commodity>{1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Re: Want to calculate market share based on the partial sums

SergeyMak — Tue, 11 Nov 2014 07:46:12 GMT

Hi,

Try to use this expression

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Sum( total <Commodity> {1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Regards,

Sergey

Re: Want to calculate market share based on the partial sums

Anonymous — Tue, 11 Nov 2014 07:53:10 GMT

Kiran,

Try the expression below to reachyour requirement and also to get the % in Total rows (total commodity / total spent):

=if(Dimensionality()=2,

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

/Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>} TOTAL <Commodity> Spend),

if(Dimensionality()=1,

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

/Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>} TOTAL Spend)))

Marc.

Re: Want to calculate market share based on the partial sums

kkkumar82 — Tue, 11 Nov 2014 08:01:53 GMT

No ,

I didnt get it, every market share converted to 100% , even I tried with <commodity> and <supplier>

Any more suggestions ??

Re: Want to calculate market share based on the partial sums

jagan — Tue, 11 Nov 2014 08:03:42 GMT

Hi,

Try this expression you need to use Commodity in Total like below

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>} TOTAL <Commodity> Spend)

TOTAL <Commodity> - This will ignore the Commodity dimension and you will get the Commodity wise total.

Hope this helps you.

Regards,

Jagan.

Re: Want to calculate market share based on the partial sums

MK_QSL — Tue, 11 Nov 2014 08:05:49 GMT

Sum({<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Sum( total <Commodity>{<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Re: Want to calculate market share based on the partial sums

Anonymous — Tue, 11 Nov 2014 08:09:16 GMT

Kiran,

Use my expression posted before in order to avoid 100% values.

Marc.

Re: Want to calculate market share based on the partial sums

kkkumar82 — Tue, 11 Nov 2014 08:13:49 GMT

Hi Marc,

All my market share values turned to 100% except the partial sums.

Thank You

Kiran

Re: Want to calculate market share based on the partial sums

MK_QSL — Tue, 11 Nov 2014 08:15:47 GMT

Can you provide sample data or sample apps.....

Re: Want to calculate market share based on the partial sums

Anonymous — Tue, 11 Nov 2014 08:19:11 GMT

The expression should work...

Could you please provide us a sample qvw?

Thank you.

Marc.

Re: Want to calculate market share based on the partial sums

kkkumar82 — Tue, 11 Nov 2014 08:21:59 GMT

I changed it but the same result as above even this time the partial sums changed to 100%

kiran

Re: Want to calculate market share based on the partial sums(second dimension percentages)

kkkumar82 — Mon, 30 Mar 2015 12:37:34 GMT

Finally got it , when we have calculated dimension we have to use advanced aggregation using aggr().

The following post helped me alot

Quick Development Tips: Qlikview: Aggregation 1: How to calculate percentages at second dimension level

The expression which I have used for market share is

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)

Aggr(NODISTINCT Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend),Commodity)

Thanks

Kiran Kumar .CH