3 Replies Latest reply on Aug 31, 2016 1:14 PM by chris.moore.11

    Help with calculation

    Aman Cheema

      I have the table below and I want to find out what which unit has more void instead of normal sales (transaction Type) but I don't want to count voids ((transaction Type) that have a normal sale for the the same product in one counter type.

       

      so if you look at the example counter sale 2165 should not count product number 252965 as a void because there is a normal sale on the same counter.  I only want it to count it if didn't have the same product number in the normal sale.

       

      sorry I know it's a little confusing so if you have any questions please ask.  Thank you for taking the time to help.

       

       

       

       

       

       

       

      Dateunit numberRegister NoCounterTransaction TypeProduct No
      016/08/29717484568712165Current Void252965
      2016/08/29717484568712165Current Void - Sale252965
      2016/08/2913585612165Normal Sale1743
      2016/08/2913585612165Normal Sale252932
      2016/08/2913585612165Normal Sale252965
      2016/08/2913585612165Normal Sale296608
      2016/08/2913585612165Normal Sale739045

       

       

       

      table of data

       

           

      Dateunit numberRegister NoCounterTransaction TypeProduct No
      2016/08/2971028853212171Normal Sale45302
      2016/08/29710288855212171Normal Sale59527
      2016/08/29710288555512172Normal Sale123323
      2016/08/29710288555512173Normal Sale296566
      2016/08/29710288555512173Normal Sale300368
      2016/08/29710288555512173Normal Sale361634
      2016/08/29710288555512173Normal Sale417626
      2016/08/29710288555512175Normal Sale824920
      2016/08/29710288555512176Normal Sale533026
      2016/08/29710288555512176Normal Sale859132
      2016/08/29710288555512269Normal Sale224501
      2016/08/29710288555512269Normal Sale881144
      2016/08/29710288555512270Normal Sale486621
      2016/08/29710288555512270Normal Sale833236
      2016/08/29710288555512271Normal Sale3228
      2016/08/29710288555512271Normal Sale156455
      2016/08/29710288555512271Normal Sale265199
      2016/08/29710288555512272Normal Sale606285
      2016/08/29710288555512273Normal Sale145433
      2016/08/29710288555512275Normal Sale93021
      2016/08/29710288555512276Normal Sale778126
      2016/08/29710288555512278Normal Sale560565
      2016/08/29710288555512279Normal Sale749267
      2016/08/29710288555512279Normal Sale767764
      2016/08/29710288555512280Normal Sale120253
      2016/08/29710288555512282Normal Sale464750
      2016/08/29710288555512282Normal Sale585737
      2016/08/29710288555512135Normal Sale305375
      2016/08/29710288555512136Normal Sale154666
      2016/08/29710288555512136Normal Sale265199
      2016/08/29710288555512136Normal Sale473090
      2016/08/29710288555512136Normal Sale752410
      2016/08/29710288555512137Normal Sale908665
      2016/08/29710288555512139Normal Sale117
      2016/08/29710288555512139Normal Sale451153
      2016/08/29710288555512140Normal Sale114314
      2016/08/29717484568712140Normal Sale114322
      2016/08/29717484568712140Normal Sale125617
      2016/08/29717484568712140Normal Sale129163
      2016/08/29717484568712140Normal Sale175372
      2016/08/29717484568712140Normal Sale400531
      2016/08/29717484568712140Normal Sale456178
      2016/08/29717484568712140Normal Sale613752
      2016/08/29717484568712140Normal Sale633206
      2016/08/29717484568712140Normal Sale643882
      2016/08/29717484568712141Normal Sale464750
      2016/08/29717484568712141Normal Sale651471
      2016/08/29717484568712143Normal Sale824920
      2016/08/29717484568712144Normal Sale906644
      2016/08/29717484568712145Current Void61887
      2016/08/29717484568712145Current Void - Sale61887
      2016/08/29717484568712145Normal Sale61887
      2016/08/29717484568712145Normal Sale549055
      2016/08/29717484568712146Normal Sale72793
      2016/08/29717484568712146Normal Sale315457
      2016/08/29717484568712146Normal Sale349910
      2016/08/29717484568712147Normal Sale2600
      2016/08/29717484568712148Normal Sale111310
      2016/08/29717484568712149Normal Sale316570
      2016/08/29717484568712150Normal Sale168971
      2016/08/29717484568712151Normal Sale3228
      2016/08/29717484568712151Normal Sale135178
      2016/08/29717484568712152Normal Sale531392
      2016/08/29717484568712153Normal Sale3871
      2016/08/29717484568712153Normal Sale904334
      2016/08/29717484568712154Normal Sale3228
      2016/08/29717484568712155Normal Sale498832
      2016/08/29717484568712155Normal Sale586560
      2016/08/29717484568712155Normal Sale599274
      2016/08/29717484568712156Normal Sale275594
      2016/08/29717484568712156Normal Sale284620
      2016/08/29717484568712157Normal Sale111302
      2016/08/29717484568712157Normal Sale316570
      2016/08/29717484568712158Normal Sale560565
      2016/08/29717484568712159Normal Sale123422
      2016/08/29717484568712159Normal Sale152363
      2016/08/29717484568712159Normal Sale177808
      2016/08/29717484568712159Normal Sale205351
      2016/08/29717484568712159Normal Sale287805
      2016/08/29717484568712159Normal Sale853226
      2016/08/29717484568712160Normal Sale100594
      2016/08/29717484568712160Normal Sale298679
      2016/08/29717484568712161Normal Sale9118
      2016/08/29717484568712162Normal Sale699983
      2016/08/29717484568712163Normal Sale199927
      2016/08/29717484568712164Normal Sale2808
      2016/08/29717484568712164Normal Sale498824
      2016/08/29717484568712165Current Void252965
      2016/08/29717484568712165Current Void - Sale252965
      2016/08/2913585612165Normal Sale1743
      2016/08/2913585612165Normal Sale252932
      2016/08/2913585612165Normal Sale252965
      2016/08/2913585612165Normal Sale296608
      2016/08/2913585612165Normal Sale739045
      2016/08/2913585612166Normal Sale303917
      2016/08/2913585612166Normal Sale879049
      2016/08/2913585612167Normal Sale60335
      2016/08/2913585612167Normal Sale549642
      2016/08/2913585612168Normal Sale530683
      2016/08/2913585612180Current Void793810
      2016/08/2913585612180Current Void - Sale793810
      2016/08/2913585612180Normal Sale10157
      2016/08/2913585612180Normal Sale172163
      2016/08/2913585612180Normal Sale240333
      2016/08/2913585612180Normal Sale252965
      2016/08/2913585612180Normal Sale361527
      2016/08/2913585612180Normal Sale500504
      2016/08/2913585612180Normal Sale793810
      2016/08/2913585612180Normal Sale838706
      2016/08/2913585612180Normal Sale843052
      2016/08/2913585612180Normal Sale874131
      2016/08/2913585612181Normal Sale400754
      2016/08/2913585612215Normal Sale484618
      2016/08/2913585612215Normal Sale667907
      2016/08/2971748489512216Normal Sale904664
      2016/08/2971748489512217Normal Sale100594
      2016/08/2971748489512217Normal Sale560144
      2016/08/2971748489512218Normal Sale2600
      2016/08/2971748489512218Normal Sale153213
      2016/08/2971748489512218Normal Sale413799
      2016/08/2971748489512218Normal Sale582973
      2016/08/2971748489512219Normal Sale201376
      2016/08/2971748489512219Normal Sale300368
      2016/08/2971748489512219Normal Sale316570
      2016/08/2971748489512220Normal Sale115972
      2016/08/2971748489512220Normal Sale536227
      2016/08/2971748489512220Normal Sale608216
      2016/08/2971748489512220Normal Sale882027
      2016/08/2971748489512221Normal Sale316570
      2016/08/2971748489512223Normal Sale547646
      2016/08/2971748489512224Normal Sale749267
      2016/08/2971748489512224Normal Sale844399
      2016/08/2971748489512225Normal Sale5793
      2016/08/2971748489512225Normal Sale736637
      2016/08/2971748489512226Normal Sale63149
      2016/08/2971748489512226Normal Sale351403
      2016/08/2971748489512228Normal Sale929620
      2016/08/2971748489512229Normal Sale3186
      2016/08/2971748489512229Normal Sale231167
      2016/08/2971748489512229Normal Sale515643
      2016/08/2971748489512229Normal Sale777607
      2016/08/2971748489512230Normal Sale143420
      2016/08/2971748489512230Normal Sale563130
      2016/08/2971748489512231Normal Sale61887
      2016/08/2971748489512231Normal Sale430595
      2016/08/2971748489512231Normal Sale543819
      2016/08/2971748489512231Normal Sale543876
      2016/08/2971748489512231Normal Sale834267
      2016/08/2971748489512232Normal Sale417626
      2016/08/2971748489512232Normal Sale525865
      2016/08/2971748489512232Normal Sale596700
      2016/08/2971748489512232Normal Sale618355
      2016/08/2971748489512233Normal Sale155051
      2016/08/2971748489512233Normal Sale627802
      2016/08/2971748489512234Normal Sale255943
      2016/08/2971748489512234Normal Sale380121
      2016/08/2971748489512235Normal Sale26567
      2016/08/2971748489512236Normal Sale610758
      2016/08/2971748489512237Normal Sale123422
      2016/08/2971748489512237Normal Sale560037
      2016/08/2971748489512237Normal Sale792317
      2016/08/2971748489512238Normal Sale615369
      2016/08/2971748489512239Normal Sale100594
      2016/08/2971748489512239Normal Sale494781
      2016/08/2971748489512240Normal Sale71126
      2016/08/2971748489512241Normal Sale773002
      2016/08/2971748489512242Normal Sale435743
      2016/08/2971748489512242Normal Sale736637
      2016/08/2971748489512243Normal Sale316570
      2016/08/2971748489512243Normal Sale417626
      2016/08/2971748489512243Normal Sale525865
      2016/08/2971748489512243Normal Sale772004
      2016/08/2971748489512244Normal Sale154666
      2016/08/2971748489512245Normal Sale12849
      2016/08/2971748489512246Normal Sale241950
      2016/08/2971748489512247Normal Sale612697
      2016/08/2971748489512250Normal Sale135178
      2016/08/2971748489512250Normal Sale375709
      2016/08/2971748489512251Normal Sale276188
      2016/08/2971748489512251Normal Sale443069
      2016/08/2971748489512252Normal Sale497206
      2016/08/2971748489512253Normal Sale314328
      2016/08/2971748489512253Normal Sale904664
      2016/08/2971748489512254Normal Sale306464
      2016/08/2971748489512254Normal Sale834333
      2016/08/2971748489512255Normal Sale74393
      2016/08/2971748489512255Normal Sale154047
      2016/08/2971748489512256Normal Sale428029
      2016/08/2971748489512258Normal Sale62851
      2016/08/2971748489512258Normal Sale685594
      2016/08/2971748489512260Normal Sale855353
      2016/08/2971748489512261Normal Sale19968
      2016/08/2971748489512262Normal Sale904334
      2016/08/2971748489512263Normal Sale374769
      2016/08/2971748489512264Normal Sale419804
      2016/08/2971748489512264Normal Sale550764
      2016/08/2971748489512265Normal Sale135178
      2016/08/2971748489512266Normal Sale12070
      2016/08/2971748489512266Normal Sale179507
      2016/08/2971748489512266Normal Sale293043
      2016/08/2971748489512266Normal Sale900118
      2016/08/2971748489512285Normal Sale632919
      2016/08/2971748489512286Normal Sale486951
      2016/08/2971748489512286Normal Sale516542
      2016/08/2971748489512286Normal Sale526897
      2016/08/2971748489512286Normal Sale756601
      2016/08/2971748489512288Normal Sale339515
      2016/08/2971748489512288Normal Sale447953
      2016/08/2971748489512288Normal Sale474866
      2016/08/2971748489512288Normal Sale824920
      2016/08/2971748489512289Normal Sale220186
      2016/08/2971748489512289Normal Sale340075
      2016/08/2971748489512289Normal Sale756999
      2016/08/2971748489512289Normal Sale904664
      2016/08/2971748489512113Normal Sale88328
      2016/08/2971748489512113Normal Sale316844
      2016/08/2971748489512114Normal Sale994145
      2016/08/2971748489512115Normal Sale54841
      2016/08/2971748489512115Normal Sale893727
      2016/08/2971748489512115Normal Sale929620
      2016/08/2971748489512116Normal Sale201467
      2016/08/2971748489512116Normal Sale850818
      2016/08/2971748489512117Normal Sale2808
      2016/08/2971748489512117Normal Sale271338
      2016/08/2971748489512118Normal Sale205518
      2016/08/2971748489512118Normal Sale438119
      2016/08/2971748489512119Normal Sale240
      2016/08/2971748489512119Normal Sale9118
      2016/08/2971748489512119Normal Sale206201
      2016/08/2971748489512119Normal Sale560698
      2016/08/2971748489512120Normal Sale34033
      2016/08/2971748489512120Normal Sale521468
      2016/08/2971748489512121Normal Sale802
      2016/08/2971748489512121Normal Sale129031
      2016/08/2971748489512122Normal Sale151100
      2016/08/2971748489512122Normal Sale154757
      2016/08/2971748489512123Normal Sale535567
      2016/08/2971748489512123Normal Sale867655
      2016/08/2971748489512124Normal Sale931733
      2016/08/2971748489512125Normal Sale231167
      2016/08/2971748489512126Normal Sale342360
      2016/08/2971748489512126Normal Sale630780
      2016/08/2971748489512126Normal Sale921304
      2016/08/2971748489512127Normal Sale181388
      2016/08/2971748489512127Normal Sale576306
      2016/08/2971748489512128Normal Sale129031
      2016/08/2971748489512128Normal Sale591644
      2016/08/2971748489512129Normal Sale18812
      2016/08/2971748489512129Normal Sale124362
      2016/08/2971748489512129Normal Sale389544
      2016/08/2971748489512129Normal Sale681411
      2016/08/2971748489512130Normal Sale550764
      2016/08/2971748489512130Normal Sale673442
      2016/08/2971748489512132Normal Sale19091
      2016/08/2971748489512132Normal Sale261743
      2016/08/2971748489512132Normal Sale771840
      2016/08/2971748489512184Normal Sale100594
      2016/08/2971748489512184Normal Sale312801
      2016/08/2971748489512185Normal Sale3186
      2016/08/2971748489512185Normal Sale891739
      2016/08/2971748489512187Normal Sale240
      2016/08/2971748489512187Normal Sale313908
      2016/08/2971748489512188Normal Sale449959
      2016/08/2971748489512189Normal Sale188474
      2016/08/2971748489512189Normal Sale227918
      2016/08/2971748489512189Normal Sale486639
      2016/08/2971748489512189Normal Sale644336
      2016/08/2971748489512191Normal Sale596
      2016/08/2971748489512193Normal Sale154666
      2016/08/2971748489512194Normal Sale48611
      2016/08/2971748489512194Normal Sale99408
      2016/08/2971748489512195Normal Sale53645
      2016/08/2971748489512195Normal Sale143776
      2016/08/2971748489512195Normal Sale298075
      2016/08/2971748489512195Normal Sale618504
      2016/08/2971748489512195Normal Sale627943
      2016/08/2971748489512196Normal Sale334276
      2016/08/2971748489512196Normal Sale395129
      2016/08/2971748489512197Normal Sale224501
      2016/08/29732585212197Normal Sale685594
      2016/08/29732585212198Normal Sale3228
      2016/08/29732585212199Normal Sale224501
      2016/08/29732585212200Normal Sale482448
      2016/08/29732585212203Normal Sale132902
      2016/08/29732585212203Normal Sale241778
      2016/08/29732585212204Normal Sale179325
      2016/08/29732585212204Normal Sale566836
      2016/08/29732585212204Normal Sale619163
      2016/08/29732585212204Normal Sale791467
      2016/08/29732585212206Normal Sale496547
      2016/08/29732585212207Normal Sale255380
      2016/08/29732585212207Normal Sale618454
      2016/08/29732585212207Normal Sale778126
      2016/08/29732585212210Normal Sale271247
      2016/08/29732585212210Normal Sale771840
      2016/08/29732585212211Normal Sale698878
      2016/08/29732585212211Normal Sale779272
        • 1. Re: Help with calculation
          chris.moore.11

          In your data you provided there actually is no instance where a counter and product number have a "void" but dont have a "normal". Any void transaction also has a normal in the data you gave. So for this set you could just filter out all the voids.

           

          You can approach the broader issue by doing this...

           

          This field will flag the counters. products that have a "normal" (all in this case). Lets call it [Any_Normal]...

          { FIXED [Product No], [Counter] :

          max(case [Transaction Type]

          when "Normal Sale" then 1

          else null end) }

           

          This field uses that field to flag all the "voids" that also have a "normal" as 0.

          if [Transaction Type] = "Current Void" and [Any_Normal] = 1 then 0

          elseif [Transaction Type] = "Current Void - Sale" and [Any_Normal] = 1 then 0

          else 1 end

           

          You could set that as a filter to filter out the voids with normals, or do other things depending on what you are trying to do.

           

          Hopefully this helps. See the attached.

          • 2. Re: Help with calculation
            Aman Cheema

            Hi Chris

             

            Thanks for the feedback.   I more looking to highlight the lines that just have [Current Void] and don't have a [normal sale] in the same [counter].  Will the above work for this?

             

            Thank you for the help.

            • 3. Re: Help with calculation
              chris.moore.11

              Slight changes to these then.

               

              The second field of these two would assign a 1 to any "current viod" that doesn't have a "normal" for that counter. Note that the second field uses the first one. Also note that there is no situation in your data where a counter has a "current void" but doesn't have a "normal sale".

               

              First you have to make this field, call it [Any_Normal]...

              { FIXED [Counter] :

              max(case [Transaction Type]

              when "Normal Sale" then 1

              else null end) }

               

              Then you can make this field...

              if [Transaction Type] = "Current Void" and [Any_Normal] = 0 then 1

              else 0 end