topic Re: Need Small Help in QlikView

Need Small Help

ajaykumar1 — Mon, 19 Jan 2015 06:39:16 GMT

Hi Everyone,

I need to get the below output in pivot table.I forgot basic formuale 🙂

Logic : 80/1396=5.7%

137/1396 =9.8%

Input :

Sales Id	Sales Country	Group	Sale A	Sales B	Sales C	Sales D	Sales E	Sales F	Total
234	India		80	137	401	0	778	0	1396
235	US		1622	464	483	0	5638	108	8315
236	UK		0	1	0	0	66	0	67
237	Malaysia		0	0	0	0	14521	0	14521
238	Singapore		0	0	6	0	950	0	956
239	Norway		0	255	203	0	976	0	1434
240	AU		4	110	382	0	274	0	770

Output(Background color is not important)
Sales Id	Sales Country	Sale A	Sales B	Sales C	Sales D	Sales E	Sales F
234	India	5.7%	9.8%	28.7%	0.0%	55.7%	0.0%
235	US	19.5%	5.6%	5.8%	0.0%	67.8%	1.3%
236	UK	0.0%	1.5%	0.0%	0.0%	98.5%	0.0%
237	Malaysia	0.0%	0.0%	0.0%	0.0%	100.0%	0.0%
238	Singapore	0.0%	0.0%	0.6%	0.0%	99.4%	0.0%
239	Norway	0.0%	17.8%	14.2%	0.0%	68.1%	0.0%
240	AU	0.5%	14.3%	49.6%	0.0%	35.6%	0.0%

More Thanks,

Ajay

Re: Need Small Help

senpradip007 — Mon, 19 Jan 2015 06:51:07 GMT

Try with Sum([Sales Amount]) / Sum(total [Sales Amount])

Re: Need Small Help

avinashelite — Mon, 19 Jan 2015 07:04:26 GMT

Try like this in the Expression:

sum(sales_Amount)/sum({1}sales_Amount)

Re: Need Small Help

ajaykumar1 — Mon, 19 Jan 2015 07:28:38 GMT

thanks Pradip

i tried same formula earlier also, getting below result

Logic : 80/1396=5.7% BUT AM GETTING 0.00291

137/1396 =9.8% BUT AM GETTING 0.004989

Because its calculating over all result like ;

1396

8315

14521

956

1434

770

Total =27459

So its giving result like; 80/27459 =0.00291 but we dont want this.

More thanks,

Ajay

Re: Need Small Help

avinashelite — Mon, 19 Jan 2015 07:33:55 GMT

If you want in % format then try like this:

Num(sum(sales_Amount)/sum({1}sales_Amount),'#,##0%') this will give the desired result.

Or go to numbers tab and select the expression>select percentage option

Re: Need Small Help

senpradip007 — Mon, 19 Jan 2015 07:40:30 GMT

PFA, hope it will reach your target.

Re: Need Small Help

jagan — Mon, 19 Jan 2015 07:43:56 GMT

Hi,

Try this expression

=Sum([Sales Amount]) / Sum(total <Country> [Sales Amount])

Regards,

Jagan.

Re: Need Small Help

MarcoWedel — Mon, 19 Jan 2015 08:01:31 GMT

Minor change:

=Num(Sum([Sales Amount]) / Sum(total <[Sales ID], [Sales Country]> [Sales Amount]),'##0.0%')

Hope this helps

Regards

Marco

Re: Need Small Help

ajaykumar1 — Mon, 19 Jan 2015 14:08:05 GMT

Thanks Pradip and All,

its working fine