Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Subtotals not working

Hi,

I have the following expression in a straight table. If I use Expression total or sum of the rows I get the wrong subtotals in the gross sales collumn. the dimension description is a category of product we sell.

Sum(Gross_Sales*Multiplier)*COUNT(DISTINCT TBI_UNIQUE)/SUM(Multiplier)

DESCRIPTION Net Sales Gross Sales Kgs Cases Total Cost Tea Cost Pack Cost Lab/OH Cost Actual Cost New Kgs Old Kgs
539621 533607 345521 181361 392796 132862 167182 45085 389365 26749 27089.52
Decaf41804180191190837261285142403270191190.80
Earl Grey Base1713171318569312628802241581312185184.80
Rare Teas1643216432047212568123118916812440208208.00
Earl Grey297062970621448867205498506826935342202921442144.00
Fruit & Herb754287542816042166453068112563156897485644116041603.59
Tea Flavoured1010731010731915031188714931973532035104687219751475147.49
Speciality3110883050743222481165692301297888893573210082216771727017610.83

If I change the dimensions to include the actual product line as well I get the correct total in gross sales but I want to show just the summarised data. Can anyone help me?

DESCRIPTION TBI_PART_ONLY Net Sales Gross Sales Kgs Cases Total Cost Tea Cost Pack Cost Lab/OH Cost Actual Cost New Kgs Old Kgs
539621 531789 345521 181361 392796 132862 167182 45085 389365 26878 27089.52
Speciality410-41-74318894000000000.00
Rare Teas410-40-7344242023534013711.00
Rare Teas410-40-7812020021514011511.00
Rare Teas410-40-7823030021816021811.00
Rare Teas410-40-7058323323022242220217011.00
Rare Teas410-40-796310310031491470114921.50
Rare Teas410-40-7079393041721700217822.00
Rare Teas410-40-7428989048180015522.00
Rare Teas410-40-7438989048180017722.00
Rare Teas410-40-7004856856047017000170122.00
Rare Teas410-40-705458458064494490031633.00
Rare Teas410-40-713398398062812800124133.00
Rare Teas410-40-721169169061401370314033.00
Rare Teas410-40-738133133061591570313233.00
Rare Teas410-40-756273273062882860326733.00
Rare Teas410-40-7038133133061891860312333.00
Rare Teas410-40-7039242242062272240322733.00
Rare Teas410-40-70452121062117042133.00
Rare Teas410-40-7060141141061021020010233.00
Rare Teas410-40-7064574574063893840628433.00
Rare Teas410-40-7068238238061671670016733.00
Rare Teas410-40-786295295083173150234644.00
Rare Teas410-40-7402232230102022000219255.00
Rare Teas410-40-7412232230101911890220255.00
Rare Teas410-40-70028968960106996970272655.00
Rare Teas410-40-7082782780122682680026966.00
Rare Teas410-40-7354314310123423360637966.00
Rare Teas410-40-70423143140123083050321066.00
Rare Teas410-40-70694434430123093090030966.00
Rare Teas410-40-7464924920183503420840399.00
Rare Teas410-40-75588880181271190812799.00
Rare Teas410-40-7873663660182182140427499.00
Rare Teas410-40-704673373301855053801255099.00
Rare Teas410-40-749139813980245665550106001212.00
Rare Teas410-40-7072156315630241158114001811581212.00
Rare Teas410-40-70701671670301131010111131515.00
Rare Teas410-40-7071159315930301177115502211771515.00
Rare Teas410-40-704199019900481532152201017262424.00
Speciality410-40-4562626381611131333.00
Tea Flavoured410-40-41554546163223273466.00
Speciality410-40-40749496163123263166.00
Speciality410-40-46344446163729273766.00
Fruit & Herb410-40-412848492434213104099.00
Tea Flavoured410-40-411848492439263104399.00
Tea Flavoured410-40-417828292447333105399.00
Speciality410-41-19373736243728363766.00
Speciality410-40-40815215292785723108599.00
Speciality410-40-40614914912326952413681212.00
Tea Flavoured410-40-42010910912327153414791212.00
Speciality410-45-0163030101249354948109.60
Speciality410-40-43622022018489569620991818.00
Speciality410-40-44813713714566644715731414.00
Speciality410-40-45219219221561291027201292121.00
Speciality410-40-43423223221493162867233152121.00
Tea Flavoured410-40-40918218221568150724862121.00
Speciality410-40-4191901902156128967241572121.00
Tea Flavoured410-40-41421721724641471118281672424.00
Speciality410-40-4334544542772136989301362727.00
Speciality410-40-45427727727721841509261662727.00
Tea Flavoured410-40-4432892892772133989271552727.00
Fruit & Herb410-41-5623030116215972410.72
Speciality410-45-10524724715901088410141081515.00
Tea Flavoured410-40-442289289338819214511361803333.00
Earl Grey410-40-4371396139616243279663012154954162162.00
Tea Flavoured410-40-413373373391041458913431623939.00
Speciality410-40-4293313314512027121315432764545.00
Speciality410-40-4104954954812825218616512524848.00
Speciality410-40-4245355355113633426817493615151.00
Speciality410-40-4284014015113630423817493185151.00
Speciality410-40-4255225225414432725718523235454.00
Speciality410-40-431130213025414463356418526485454.00
Speciality410-41-873/11091097674211808677.20
Speciality410-40-4304304305715230723319553015757.00
Speciality410-40-440146814685715214261347196014235757.00
Earl Grey410-45-103257257181501157522191311818.00
Speciality410-41-30441411144123542313411.20
Speciality410-40-4235585587520036727024723567575.00
Speciality410-40-4275325328121634023526783408181.00
Tea Flavoured410-40-4419959959024053041429875019090.00
Speciality410-41-525/1909054876212906754.80
Earl Grey410-40-4387447448923748937430855608989.00
Speciality410-40-4396026026224845235630664506262.00
Fruit & Herb410-41-5648383248711430276722.16
Speciality410-41-527/111211266091273208566.00
Speciality410-40-4227587581022725193883398504102102.00
Speciality410-41-30926626615551186038201671515.00
Speciality410-41-5261101106130731640176466.00
Tea Flavoured410-40-444121412149036072358844922449090.00
Speciality410-40-4211048104814137671953746136741141141.00
Speciality410-41-874/12122121412164454801531414.40
Speciality410-41-875/12182181412161424901461414.40
Speciality410-41-15018918912801214652241311212.00
Speciality410-41-15129429412801759655241751212.00
Speciality410-41-11116316312721425862221381212.00
Tea Flavoured410-41-852/147647629243801667304192928.80
Speciality410-41-83425125181815536833621287.50
Tea Flavoured410-41-83325225281814829833615487.50
Rare Teas410-41-318303303065255154891226254.50
Speciality410-41-317131131151801201292167966.00
Speciality410-41-3152832832951601765294301831212.00
Tea Flavoured410-41-851/14744742924307679802922928.80
Speciality410-41-135333333159018758101271981515.00
Speciality410-41-1534504502315024698104442542322.50
Speciality410-41-1143493492412823083107402512424.00
Speciality410-41-11238938923120274125109412602322.50
Tea Flavoured410-41-853/147847829243569711703182928.80
Speciality410-41-1394854851812022765128342371818.00
Tea Flavoured410-41-543/1618618303003626613604853030.00
Speciality410-41-510/1556556303004159416103873030.00
Tea Flavoured410-41-544/1640640303003896616305153030.00
Decaf410-41-513/17097093131252218916705883131.20
Speciality410-41-854/1476476292441510516803572928.80
Tea Flavoured410-41-17162362338250408167169724703837.50
Tea Flavoured410-41-17262762738250384140169754363837.50
Speciality410-45-107213421341461220870543172155875146146.40
Speciality410-41-31648348353128831473186543332221.60
Tea Flavoured410-41-15673573544290406126188924564443.50
Speciality410-41-10370870842224480201204764804242.00
Speciality410-41-4051502150218067510646892181571091180180.00
Fruit & Herb410-41-522/16926923636057115922005053636.00
Earl Grey Base410-41-4061713171318569312628802241581312185184.80
Tea Flavoured410-41-170870870533505422062351016275352.50
Speciality410-41-46853353362710057359241442271515.00
Tea Flavoured410-41-8306346342354437882421074562322.50
Tea Flavoured410-41-550/19139134444458210424205634444.40
Speciality410-41-47414531453149144914635253271234109.72
Speciality410-41-523/1870870484806069026105664848.00
Speciality410-41-596692692112505021182741114481111.25
Speciality410-41-59865365311250444592741114581111.25
Speciality410-41-542/1105010505151068914027706255151.00
Tea Flavoured410-41-551/11288128862624977359286010606262.40
Tea Flavoured410-41-83163563523544951002871074692322.50
Speciality410-41-47811321132165160519200290298061110.80
Tea Flavoured410-41-57859959992005086630014244799.00
Fruit & Herb410-41-15811421142684506101603071437826867.50
Speciality410-41-524877877378065501333101064353737.20
Tea Flavoured410-41-15712491249734866371683151547477372.87
Tea Flavoured410-41-18961261292505233431717256099.00
Tea Flavoured410-41-83284584530726041393221437193030.00
Tea Flavoured410-41-15212101210755006521793241487467575.00
Tea Flavoured410-41-552/1111711175454080118333007305454.00
Speciality410-41-104943943684506571973391225676867.50
Tea Flavoured410-41-17412391239755008363483391499587575.00
Speciality410-41-524/1109310934545075716934806054545.00
Fruit & Herb410-41-545/1118711875757083818534907995757.00
Tea Flavoured410-41-855/1138313838672103025335109638686.40
Earl Grey410-41-540/1974974454506798435405604545.00
Tea Flavoured410-41-10913361336743928323393561378257473.50
Speciality410-41-407249724972991123176711433622611661299299.40
Earl Grey410-41-10711811181754508022933701398497575.00
Tea Flavoured410-41-10612131213774088583353711528797776.50
Speciality410-41-13611201120573428163283801088655757.00
Speciality410-41-3301766176620293795590623871407971615.90
Speciality410-41-15515291529906008502833911768989090.00
Tea Flavoured410-41-503/116511651858521105254396011388585.20
Tea Flavoured410-41-554/1134513456565494019140008866565.40
Speciality410-41-521/115111511757501107299407010467575.00
Speciality410-41-1381202120257380130678041710813015757.00
Tea Flavoured410-41-0101767176715040013626644802181310150150.00
Speciality410-41-7441147574449452886925848113093659.60
Speciality410-41-4671013101312542001144115481884533030.00
Speciality410-41-600/112931293758751133238503010207575.00
Speciality410-41-1731865186511375012485195052241494113112.50
Tea Flavoured410-41-59512411241235008431005222218632322.50
Speciality410-41-553/11382138267672113324652909926767.20
Speciality410-41-328247224722871611258348654919810242322.50
Speciality410-41-1542003200311375012084385502201232113112.50
Speciality410-41-5971869186923520133555355223013522323.40
Speciality410-41-306100510052859530085595579238173030.00
Tea Flavoured410-41-1592303230312684012774605872291436126126.00
Speciality410-41-1601595159593558126245462818011509393.00
Speciality410-41-0042209220922560015846566292991689225225.00
Earl Grey410-41-8051665166560144115623564427712506060.00
Speciality410-41-0132770277020454417427926522981758204204.00
Speciality410-41-00524682468225600201010566542992097225225.00
Speciality410-41-520/1204420441111110161935467201433111111.00
Speciality410-41-3122440244015090016907226892801771150150.00
Earl Grey410-41-1082028202813178314875526942411551131130.50
Tea Flavoured410-41-509/1262226221281284160922669701501128128.40
Fruit & Herb410-41-6261501150123450107713770523410892322.50
Fruit & Herb410-41-6241501150123450114716470827511672322.50
Fruit & Herb410-41-6181531153123459120723273723912212322.95
Speciality410-41-01124712471255680234812387713392242255255.00
Tea Flavoured410-41-5941864186434750127514879633213313433.75
Speciality410-41-5921820182034750125812979733212663433.75
Fruit & Herb410-41-1852425242590612161862880218820069089.76
Speciality410-41-8032125212579189152837081234715297978.75
Speciality410-41-541/1211621161051050178339682701281105105.00
Speciality410-41-1341940194011468416145438652061641114114.00
Tea Flavoured410-41-5721731173127590146716388641815062726.55
Speciality410-41-859/1534953493532944232160488904188353352.80
Speciality410-41-8072290229086207160233089238016158686.25
Tea Flavoured410-41-5731790179027610151416691643215392727.45
Tea Flavoured410-41-5741769176927610150715991643214452727.45
Speciality410-41-95131121037204439721785516940328204041121.50
Speciality410-41-511/1333433341761758240450995702256176175.80
Speciality410-41-860/1412941292952463530110097403297295295.20
Tea Flavoured410-41-5772028202830660173928199146716023029.70
Speciality410-41-329463546355398621151566162103037319774242.30
Speciality410-41-591243524354510001675172106044216824545.00
Earl Grey410-41-593248924894510001678176106044217764545.00
Speciality410-41-0034715471537510003383179410904993384375375.00
Speciality410-41-505/14076407693185425113881134023519392.70
Speciality410-41-012462746273579523593197711414753364357357.00
Tea Flavoured410-41-869/1538253823382823408597114303325338338.40
Speciality410-41-13131463146153918224580911612762350153153.00
Earl Grey410-41-0084278427837510003283158511935053431375375.00
Speciality410-41-62025522552387651988314119847620503838.25
Speciality410-41-856/1406740672952463590936119902827295295.20
Speciality410-41-952225275179908551574174120519514841854.00
Earl Grey410-41-3144428442826315752743105212064862892263262.50
Tea Flavoured410-41-601/13378337818021002517368120802339180180.00
Speciality410-41-1053595359524016002783112612234332760240240.00
Decaf410-41-512/134713471160159632041096125702682160159.60
Tea Flavoured410-41-857/1470347033102583417633125703136310309.60
Speciality410-41-858/14404440431025838861102125703353310309.60
Speciality410-41-1375675567517010175623400512863325531170169.50
Speciality410-41-501/156985698288288041641309131804169288288.00
Speciality410-41-132351335132061233260484513893702421206205.50
Speciality410-41-602/13714371421024503254747140902905210210.00
Tea Flavoured410-41-579298129813610002534354149368724343636.00
Fruit & Herb410-41-61633023302509902253231155846522535049.50
Speciality410-41-507/157545754309308844531151165504201309308.80
Speciality410-41-3135875587537522503894147217237004139375375.00
Tea Flavoured410-41-576347934794412202918278180283929364443.92
Tea Flavoured410-41-504/15935593530030004049614183503727300300.00
Tea Flavoured410-41-0099324932467518006014311719629355914675675.00
Speciality410-41-1339505950528116835577294321285065365281280.50
Tea Flavoured410-41-5754856485668152039306092245107639536868.40
Speciality410-41-00296529652912243270433483234712137433912912.00
Speciality410-41-95053841795136341225038745732367933430945135.00
Tea Flavoured410-41-00695829582819218471803582238112177829819819.00
Speciality410-41-1305761576133620164371121625496063770336336.00
Fruit & Herb410-41-612537453746416113792405258580138576464.44
Speciality410-41-1027531753151834505547197626389335115518517.50
Earl Grey410-41-0071026610266882264673203449268411878074882882.00
Fruit & Herb410-41-622594459447117824136615277874344177171.28
Fruit & Herb410-41-610600460047218004500693291289546017272.00
Tea Flavoured410-41-181739273921426025685293131531788016048257256.80
Speciality410-41-60681978197682277642517163629107963276868.31
Speciality410-41-001141611416113384014109845123408117791126313381338.00
Fruit & Herb410-41-6088976897610826916422794429013396732108107.64
Speciality410-41-10114059140599665152973736994297174110593966966.00
Fruit & Herb410-41-212196821968266945631264950246219140513267669669.24
Fruit & Herb410-41-614159711597123947881212317887358297713613239239.40
Speciality410-41-502/12360723607128912888188464087788301689112891288.80
Speciality410-41-508/145351453512425242523640586321483403231324252425.20
0 Replies