Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to show Sale Vs Quota

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

3 Replies
sunny_talwar

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

Not applicable
Author

These values have no selections made. All dates are Calendar, not Fiscal. All momentary values are USD.
2006-Q32006-Q42007-Q12007-Q22007-Q32007-Q42008-Q12008-Q22008-Q32008-Q42009-Q12009-Q2
SalespersonSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuota
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-Q32006-Q42007-Q12007-Q22007-Q32007-Q42008-Q12008-Q22008-Q32008-Q42009-Q12009-Q2
SalespersonSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuotaSalesQuota
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
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand