How to show Sale Vs Quota

Fri, 25 Mar 2016 12:38:23 GMT

LOAD

SalesOrderID,

RevisionNumber,

OrderDate,

Status,

OnlineOrderFlag,

SalesOrderNumber,

PurchaseOrderNumber,

AccountNumber as SalesOrderHeader_AccountNumber,

CustomerID,

TerritoryID as SalesOrderHeader_TerritoryID ,

ShipToAddressID as AddressID,

ShipMethodID,

TaxAmt,

Freight,

LocalCurrency,

LOAD F1,

[275],

[276],

[277],

[279],

[280],

[282],

[284],

[286],

[288],

[289],

[290],

Website

FROM

[..\Certification\DeveloperCertificationV10Document\Data\Certification Data.xlsx]

(ooxml, embedded labels, table is Quota);

in second table , F1 is quarter date and 275 to 290 salesperson ID . Now I have to connect it with first table and find out the monthly quota. Please help me

Re: How to show Sale Vs Quota

sunny_talwar — Fri, 25 Mar 2016 14:42:01 GMT

Can you provide few lines of raw data with your expected output?

Re: How to show Sale Vs Quota

Fri, 25 Mar 2016 14:53:26 GMT

These values have no selections made. All dates are Calendar, not Fiscal. All momentary values are USD.
2006-Q3		2006-Q4		2007-Q1		2007-Q2		2007-Q3		2007-Q4		2008-Q1		2008-Q2		2008-Q3		2008-Q4		2009-Q1		2009-Q2
Salesperson	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota
Ansman-Wolfe, Pamela O.	$618,446	$700,000	$1,071,344	$665,000	$859,414	$665,000	$706,047	$770,000	$1,014,775	$805,000	$248,004	$765,000	-	-	-	-	-	-	-	-	-	-	-	-
Blythe, Michael G.	$217,471	$200,000	$351,075	$190,000	$287,219	$190,000	$327,156	$220,000	$982,660	$580,000	$846,866	$550,000	$561,693	$550,000	$696,189	$638,000	$943,545	$667,000	$804,164	$635,000	$624,232	$635,000	$699,305	$735,000
Carson, Jillian .	$344,711	$400,000	$606,530	$380,000	$465,867	$380,000	$607,068	$440,000	$834,132	$775,000	$718,572	$735,000	$669,686	$735,000	$853,016	$853,000	$750,814	$850,000	$590,423	$800,000	$475,330	$800,000	$581,127	$935,000
Mensa-Annan, Tete A.	-	-	-	-	-	-	-	-	-	-	$642,859	$665,000	$682,808	$665,000	$798,513	$770,000	$1,717,249	$1,400,000	$1,441,965	$1,330,000	$1,164,712	$1,330,000	$1,468,939	$1,540,000
Mitchell, Linda C.	$795,748	$850,000	$1,097,830	$808,000	$1,010,045	$808,000	$985,617	$935,000	$2,410,670	$1,600,000	$1,859,674	$1,520,000	$1,353,316	$1,520,000	$1,818,366	$1,750,000	$2,197,166	$1,850,000	$1,762,925	$1,750,000	$1,439,789	$1,750,000	$1,735,314	$2,035,000
Pak, Jae B.	-	-	-	-	-	-	-	-	$1,860,335	$1,250,000	$1,396,217	$1,175,000	$1,008,618	$1,175,000	$1,349,999	$1,375,000	$1,744,784	$1,400,000	$1,547,904	$1,330,000	$1,024,146	$1,330,000	$1,366,116	$1,540,000
Reiter, Tsvi Michael.	$579,275	$550,000	$869,647	$525,000	$670,885	$525,000	$737,241	$605,000	$781,884	$630,000	$625,893	$600,000	$477,543	$600,000	$594,869	$693,000	$741,878	$725,000	$614,990	$680,000	$500,264	$680,000	$673,047	$795,000
Saraiva, José Edvaldo.	$637,983	$700,000	$875,377	$665,000	$775,755	$665,000	$790,692	$770,000	$471,783	$450,000	$369,975	$425,000	$273,907	$425,000	$290,828	$495,000	$862,227	$660,000	$733,185	$627,000	$542,464	$627,000	$734,641	$726,000
Tsoflias, Lynn N.	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	$450,884	$450,000	$396,547	$425,000	$340,479	$425,000	$406,425	$495,000
Valdez, Rachel B.	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	$571,523	$450,000	$544,651	$425,000	$405,962	$425,000	$498,959	$495,000
Varkey Chudukatil, Ranjit R.	-	-	-	-	-	-	-	-	$523,999	$350,000	$333,124	$330,000	$238,773	$330,000	$332,125	$385,000	$957,497	$725,000	$845,410	$685,000	$597,368	$685,000	$779,243	$795,000
Website Sales	$1,453,523	$1,500,000	$1,812,851	$1,425,000	$1,791,698	$1,425,000	$2,014,012	$1,650,000	$1,396,834	$1,500,000	$1,327,799	$1,425,000	$1,413,530	$1,425,000	$1,623,971	$1,650,000	$2,744,340	$2,750,000	$4,009,218	$3,450,000	$4,283,630	$4,300,000	$5,436,429	$5,375,000
Total	$4,647,157	$4,900,000	$6,684,652	$4,658,000	$5,860,884	$4,658,000	$6,167,833	$5,390,000	$10,277,073	$7,940,000	$8,368,983	$8,190,000	$6,679,874	$7,425,000	$8,357,875	$8,609,000	$13,681,907	$11,927,000	$13,291,381	$12,137,000	$11,398,376	$12,987,000	$14,379,545	$15,466,000
These values have no selections made. All dates are Calendar, not Fiscal. All momentary values are USD.
2006-Q3		2006-Q4		2007-Q1		2007-Q2		2007-Q3		2007-Q4		2008-Q1		2008-Q2		2008-Q3		2008-Q4		2009-Q1		2009-Q2
Salesperson	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota	Sales	Quota
Ansman-Wolfe, Pamela O.	$119,807	$133,333	$211,503	$126,667	$143,953	$126,667	$447,778	$146,667	$763,213	$690,000	$999,151	$806,786	-	-	-	-	-	-	-	-	-	-	-	-
Blythe, Michael G.	($17,061)	($16,667)	($20,904)	($15,833)	($52,820)	($15,833)	$398,288	($18,333)	$672,892	$675,000	$1,034,849	$807,321	$586,534	$790,714	$745,841	$918,571	$5,481,588	$4,858,742	$5,727,172	$5,124,288	$5,118,842	$5,652,318	$6,495,528	$6,802,258
Carson, Jillian .	($153,929)	($166,667)	($253,311)	($158,333)	($249,594)	($158,333)	$348,799	($183,333)	$582,570	$660,000	$1,070,548	$807,857	$553,611	$785,893	$703,666	$913,429	$5,974,091	$5,300,421	$6,267,470	$5,601,435	$5,614,090	$6,188,906	$7,126,134	$7,452,195
Mensa-Annan, Tete A.	-	-	-	-	-	-	-	-	-	-	$1,106,246	$808,393	$520,688	$781,071	$661,492	$908,286	$6,466,594	$5,742,099	$6,807,769	$6,078,582	$6,109,338	$6,725,493	$7,756,740	$8,102,132
Mitchell, Linda C.	$795,748	$850,000	$1,097,830	$808,000	$1,010,045	$808,000	$985,617	$935,000	($342,610)	($35,000)	$1,141,945	$808,929	$487,766	$776,250	$619,317	$903,143	$6,959,098	$6,183,777	$7,348,068	$6,555,728	$6,604,586	$7,262,080	$8,387,345	$8,752,069
Pak, Jae B.	-	-	-	-	-	-	-	-	($1,032,121)	($442,000)	$1,177,643	$809,464	$454,843	$771,429	$577,142	$898,000	$7,451,601	$6,625,456	$7,888,366	$7,032,875	$7,099,834	$7,798,668	$9,017,951	$9,402,006
Reiter, Tsvi Michael.	$696,691	$850,000	$881,107	$805,000	$880,626	$805,000	$844,143	$935,000	($1,721,632)	($849,000)	$1,213,342	$810,000	$421,920	$766,607	$534,968	$892,857	$7,944,104	$7,067,134	$8,428,665	$7,510,022	$7,595,082	$8,335,255	$9,648,557	$10,051,943
Saraiva, José Edvaldo.	$755,399	$1,000,000	$886,837	$945,000	$985,496	$945,000	$897,593	$1,100,000	($2,411,143)	($1,256,000)	$1,249,040	$810,536	$388,997	$761,786	$492,793	$887,714	$8,436,607	$7,508,812	$8,968,964	$7,987,169	$8,090,330	$8,871,843	$10,279,163	$10,701,880
Tsoflias, Lynn N.	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	$8,929,110	$7,950,491	$9,509,262	$8,464,316	$8,585,578	$9,408,430	$10,909,768	$11,351,817
Valdez, Rachel B.	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	$9,421,613	$8,392,169	$10,049,561	$8,941,463	$9,080,826	$9,945,017	$11,540,374	$12,001,754
Varkey Chudukatil, Ranjit R.	-	-	-	-	-	-	-	-	$13,819,042	$10,853,333	$11,379,161	$11,175,000	$9,218,493	$10,155,000	$11,463,740	$11,772,000	$9,914,116	$8,833,847	$10,589,860	$9,418,610	$9,576,074	$10,481,605	$12,170,980	$12,651,691
Website Sales	$7,840,791	$8,300,000	$11,556,453	$7,891,000	$9,930,071	$7,891,000	$10,321,653	$9,130,000	$18,695,579	$14,648,333	$15,397,091	$15,105,000	$12,439,044	$13,702,500	$15,476,616	$15,884,000	$10,406,619	$9,275,526	$11,130,158	$9,895,756	$10,071,322	$11,018,192	$12,801,585	$13,301,628
Total	$11,034,425	$11,700,000	$16,428,255	$11,124,000	$13,999,257	$11,124,000	$14,475,473	$12,870,000	$23,572,116	$18,443,333	$19,415,020	$19,035,000	$15,659,595	$17,250,000	$19,489,491	$19,996,000	$10,899,123	$9,717,204	$11,670,457	$10,372,903	$10,566,570	$11,554,780	$13,432,191	$13,951,565
Total	$962,717	$2,044,600	$1,639,840	$1,358,050	$2,868,129	$2,458,472	$1,309,863	$2,451,606	$2,099,416	$1,546,592	$2,942,673	$1,678,567	$2,894,055	$4,147,192	$3,235,826	$2,217,544	$3,388,911	$2,762,527	$1,756,407	$2,873,937	$2,049,530	$2,371,678	$3,443,525	$2,542,672

Re: How to show Sale Vs Quota

Gysbert_Wassenaar — Fri, 25 Mar 2016 15:42:29 GMT

Your first table doesn't contain a field that contains the salesperson ID. The second table doesn't contain information about sales orders. That means there is now way to associate the tables properly. Your tables are not related at all.

topic Re: How to show Sale Vs Quota in QlikView

How to show Sale Vs Quota

Re: How to show Sale Vs Quota

Re: How to show Sale Vs Quota

Re: How to show Sale Vs Quota