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

# Help with calculation

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.

 Date unit number Register No Counter Transaction Type Product No
 016/08/29 7174845687 1 2165 Current Void 252965 2016/08/29 7174845687 1 2165 Current Void - Sale 252965 2016/08/29 135856 1 2165 Normal Sale 1743 2016/08/29 135856 1 2165 Normal Sale 252932 2016/08/29 135856 1 2165 Normal Sale 252965 2016/08/29 135856 1 2165 Normal Sale 296608 2016/08/29 135856 1 2165 Normal Sale 739045

table of data

 Date unit number Register No Counter Transaction Type Product No 2016/08/29 710288532 1 2171 Normal Sale 45302 2016/08/29 7102888552 1 2171 Normal Sale 59527 2016/08/29 7102885555 1 2172 Normal Sale 123323 2016/08/29 7102885555 1 2173 Normal Sale 296566 2016/08/29 7102885555 1 2173 Normal Sale 300368 2016/08/29 7102885555 1 2173 Normal Sale 361634 2016/08/29 7102885555 1 2173 Normal Sale 417626 2016/08/29 7102885555 1 2175 Normal Sale 824920 2016/08/29 7102885555 1 2176 Normal Sale 533026 2016/08/29 7102885555 1 2176 Normal Sale 859132 2016/08/29 7102885555 1 2269 Normal Sale 224501 2016/08/29 7102885555 1 2269 Normal Sale 881144 2016/08/29 7102885555 1 2270 Normal Sale 486621 2016/08/29 7102885555 1 2270 Normal Sale 833236 2016/08/29 7102885555 1 2271 Normal Sale 3228 2016/08/29 7102885555 1 2271 Normal Sale 156455 2016/08/29 7102885555 1 2271 Normal Sale 265199 2016/08/29 7102885555 1 2272 Normal Sale 606285 2016/08/29 7102885555 1 2273 Normal Sale 145433 2016/08/29 7102885555 1 2275 Normal Sale 93021 2016/08/29 7102885555 1 2276 Normal Sale 778126 2016/08/29 7102885555 1 2278 Normal Sale 560565 2016/08/29 7102885555 1 2279 Normal Sale 749267 2016/08/29 7102885555 1 2279 Normal Sale 767764 2016/08/29 7102885555 1 2280 Normal Sale 120253 2016/08/29 7102885555 1 2282 Normal Sale 464750 2016/08/29 7102885555 1 2282 Normal Sale 585737 2016/08/29 7102885555 1 2135 Normal Sale 305375 2016/08/29 7102885555 1 2136 Normal Sale 154666 2016/08/29 7102885555 1 2136 Normal Sale 265199 2016/08/29 7102885555 1 2136 Normal Sale 473090 2016/08/29 7102885555 1 2136 Normal Sale 752410 2016/08/29 7102885555 1 2137 Normal Sale 908665 2016/08/29 7102885555 1 2139 Normal Sale 117 2016/08/29 7102885555 1 2139 Normal Sale 451153 2016/08/29 7102885555 1 2140 Normal Sale 114314 2016/08/29 7174845687 1 2140 Normal Sale 114322 2016/08/29 7174845687 1 2140 Normal Sale 125617 2016/08/29 7174845687 1 2140 Normal Sale 129163 2016/08/29 7174845687 1 2140 Normal Sale 175372 2016/08/29 7174845687 1 2140 Normal Sale 400531 2016/08/29 7174845687 1 2140 Normal Sale 456178 2016/08/29 7174845687 1 2140 Normal Sale 613752 2016/08/29 7174845687 1 2140 Normal Sale 633206 2016/08/29 7174845687 1 2140 Normal Sale 643882 2016/08/29 7174845687 1 2141 Normal Sale 464750 2016/08/29 7174845687 1 2141 Normal Sale 651471 2016/08/29 7174845687 1 2143 Normal Sale 824920 2016/08/29 7174845687 1 2144 Normal Sale 906644 2016/08/29 7174845687 1 2145 Current Void 61887 2016/08/29 7174845687 1 2145 Current Void - Sale 61887 2016/08/29 7174845687 1 2145 Normal Sale 61887 2016/08/29 7174845687 1 2145 Normal Sale 549055 2016/08/29 7174845687 1 2146 Normal Sale 72793 2016/08/29 7174845687 1 2146 Normal Sale 315457 2016/08/29 7174845687 1 2146 Normal Sale 349910 2016/08/29 7174845687 1 2147 Normal Sale 2600 2016/08/29 7174845687 1 2148 Normal Sale 111310 2016/08/29 7174845687 1 2149 Normal Sale 316570 2016/08/29 7174845687 1 2150 Normal Sale 168971 2016/08/29 7174845687 1 2151 Normal Sale 3228 2016/08/29 7174845687 1 2151 Normal Sale 135178 2016/08/29 7174845687 1 2152 Normal Sale 531392 2016/08/29 7174845687 1 2153 Normal Sale 3871 2016/08/29 7174845687 1 2153 Normal Sale 904334 2016/08/29 7174845687 1 2154 Normal Sale 3228 2016/08/29 7174845687 1 2155 Normal Sale 498832 2016/08/29 7174845687 1 2155 Normal Sale 586560 2016/08/29 7174845687 1 2155 Normal Sale 599274 2016/08/29 7174845687 1 2156 Normal Sale 275594 2016/08/29 7174845687 1 2156 Normal Sale 284620 2016/08/29 7174845687 1 2157 Normal Sale 111302 2016/08/29 7174845687 1 2157 Normal Sale 316570 2016/08/29 7174845687 1 2158 Normal Sale 560565 2016/08/29 7174845687 1 2159 Normal Sale 123422 2016/08/29 7174845687 1 2159 Normal Sale 152363 2016/08/29 7174845687 1 2159 Normal Sale 177808 2016/08/29 7174845687 1 2159 Normal Sale 205351 2016/08/29 7174845687 1 2159 Normal Sale 287805 2016/08/29 7174845687 1 2159 Normal Sale 853226 2016/08/29 7174845687 1 2160 Normal Sale 100594 2016/08/29 7174845687 1 2160 Normal Sale 298679 2016/08/29 7174845687 1 2161 Normal Sale 9118 2016/08/29 7174845687 1 2162 Normal Sale 699983 2016/08/29 7174845687 1 2163 Normal Sale 199927 2016/08/29 7174845687 1 2164 Normal Sale 2808 2016/08/29 7174845687 1 2164 Normal Sale 498824 2016/08/29 7174845687 1 2165 Current Void 252965 2016/08/29 7174845687 1 2165 Current Void - Sale 252965 2016/08/29 135856 1 2165 Normal Sale 1743 2016/08/29 135856 1 2165 Normal Sale 252932 2016/08/29 135856 1 2165 Normal Sale 252965 2016/08/29 135856 1 2165 Normal Sale 296608 2016/08/29 135856 1 2165 Normal Sale 739045 2016/08/29 135856 1 2166 Normal Sale 303917 2016/08/29 135856 1 2166 Normal Sale 879049 2016/08/29 135856 1 2167 Normal Sale 60335 2016/08/29 135856 1 2167 Normal Sale 549642 2016/08/29 135856 1 2168 Normal Sale 530683 2016/08/29 135856 1 2180 Current Void 793810 2016/08/29 135856 1 2180 Current Void - Sale 793810 2016/08/29 135856 1 2180 Normal Sale 10157 2016/08/29 135856 1 2180 Normal Sale 172163 2016/08/29 135856 1 2180 Normal Sale 240333 2016/08/29 135856 1 2180 Normal Sale 252965 2016/08/29 135856 1 2180 Normal Sale 361527 2016/08/29 135856 1 2180 Normal Sale 500504 2016/08/29 135856 1 2180 Normal Sale 793810 2016/08/29 135856 1 2180 Normal Sale 838706 2016/08/29 135856 1 2180 Normal Sale 843052 2016/08/29 135856 1 2180 Normal Sale 874131 2016/08/29 135856 1 2181 Normal Sale 400754 2016/08/29 135856 1 2215 Normal Sale 484618 2016/08/29 135856 1 2215 Normal Sale 667907 2016/08/29 717484895 1 2216 Normal Sale 904664 2016/08/29 717484895 1 2217 Normal Sale 100594 2016/08/29 717484895 1 2217 Normal Sale 560144 2016/08/29 717484895 1 2218 Normal Sale 2600 2016/08/29 717484895 1 2218 Normal Sale 153213 2016/08/29 717484895 1 2218 Normal Sale 413799 2016/08/29 717484895 1 2218 Normal Sale 582973 2016/08/29 717484895 1 2219 Normal Sale 201376 2016/08/29 717484895 1 2219 Normal Sale 300368 2016/08/29 717484895 1 2219 Normal Sale 316570 2016/08/29 717484895 1 2220 Normal Sale 115972 2016/08/29 717484895 1 2220 Normal Sale 536227 2016/08/29 717484895 1 2220 Normal Sale 608216 2016/08/29 717484895 1 2220 Normal Sale 882027 2016/08/29 717484895 1 2221 Normal Sale 316570 2016/08/29 717484895 1 2223 Normal Sale 547646 2016/08/29 717484895 1 2224 Normal Sale 749267 2016/08/29 717484895 1 2224 Normal Sale 844399 2016/08/29 717484895 1 2225 Normal Sale 5793 2016/08/29 717484895 1 2225 Normal Sale 736637 2016/08/29 717484895 1 2226 Normal Sale 63149 2016/08/29 717484895 1 2226 Normal Sale 351403 2016/08/29 717484895 1 2228 Normal Sale 929620 2016/08/29 717484895 1 2229 Normal Sale 3186 2016/08/29 717484895 1 2229 Normal Sale 231167 2016/08/29 717484895 1 2229 Normal Sale 515643 2016/08/29 717484895 1 2229 Normal Sale 777607 2016/08/29 717484895 1 2230 Normal Sale 143420 2016/08/29 717484895 1 2230 Normal Sale 563130 2016/08/29 717484895 1 2231 Normal Sale 61887 2016/08/29 717484895 1 2231 Normal Sale 430595 2016/08/29 717484895 1 2231 Normal Sale 543819 2016/08/29 717484895 1 2231 Normal Sale 543876 2016/08/29 717484895 1 2231 Normal Sale 834267 2016/08/29 717484895 1 2232 Normal Sale 417626 2016/08/29 717484895 1 2232 Normal Sale 525865 2016/08/29 717484895 1 2232 Normal Sale 596700 2016/08/29 717484895 1 2232 Normal Sale 618355 2016/08/29 717484895 1 2233 Normal Sale 155051 2016/08/29 717484895 1 2233 Normal Sale 627802 2016/08/29 717484895 1 2234 Normal Sale 255943 2016/08/29 717484895 1 2234 Normal Sale 380121 2016/08/29 717484895 1 2235 Normal Sale 26567 2016/08/29 717484895 1 2236 Normal Sale 610758 2016/08/29 717484895 1 2237 Normal Sale 123422 2016/08/29 717484895 1 2237 Normal Sale 560037 2016/08/29 717484895 1 2237 Normal Sale 792317 2016/08/29 717484895 1 2238 Normal Sale 615369 2016/08/29 717484895 1 2239 Normal Sale 100594 2016/08/29 717484895 1 2239 Normal Sale 494781 2016/08/29 717484895 1 2240 Normal Sale 71126 2016/08/29 717484895 1 2241 Normal Sale 773002 2016/08/29 717484895 1 2242 Normal Sale 435743 2016/08/29 717484895 1 2242 Normal Sale 736637 2016/08/29 717484895 1 2243 Normal Sale 316570 2016/08/29 717484895 1 2243 Normal Sale 417626 2016/08/29 717484895 1 2243 Normal Sale 525865 2016/08/29 717484895 1 2243 Normal Sale 772004 2016/08/29 717484895 1 2244 Normal Sale 154666 2016/08/29 717484895 1 2245 Normal Sale 12849 2016/08/29 717484895 1 2246 Normal Sale 241950 2016/08/29 717484895 1 2247 Normal Sale 612697 2016/08/29 717484895 1 2250 Normal Sale 135178 2016/08/29 717484895 1 2250 Normal Sale 375709 2016/08/29 717484895 1 2251 Normal Sale 276188 2016/08/29 717484895 1 2251 Normal Sale 443069 2016/08/29 717484895 1 2252 Normal Sale 497206 2016/08/29 717484895 1 2253 Normal Sale 314328 2016/08/29 717484895 1 2253 Normal Sale 904664 2016/08/29 717484895 1 2254 Normal Sale 306464 2016/08/29 717484895 1 2254 Normal Sale 834333 2016/08/29 717484895 1 2255 Normal Sale 74393 2016/08/29 717484895 1 2255 Normal Sale 154047 2016/08/29 717484895 1 2256 Normal Sale 428029 2016/08/29 717484895 1 2258 Normal Sale 62851 2016/08/29 717484895 1 2258 Normal Sale 685594 2016/08/29 717484895 1 2260 Normal Sale 855353 2016/08/29 717484895 1 2261 Normal Sale 19968 2016/08/29 717484895 1 2262 Normal Sale 904334 2016/08/29 717484895 1 2263 Normal Sale 374769 2016/08/29 717484895 1 2264 Normal Sale 419804 2016/08/29 717484895 1 2264 Normal Sale 550764 2016/08/29 717484895 1 2265 Normal Sale 135178 2016/08/29 717484895 1 2266 Normal Sale 12070 2016/08/29 717484895 1 2266 Normal Sale 179507 2016/08/29 717484895 1 2266 Normal Sale 293043 2016/08/29 717484895 1 2266 Normal Sale 900118 2016/08/29 717484895 1 2285 Normal Sale 632919 2016/08/29 717484895 1 2286 Normal Sale 486951 2016/08/29 717484895 1 2286 Normal Sale 516542 2016/08/29 717484895 1 2286 Normal Sale 526897 2016/08/29 717484895 1 2286 Normal Sale 756601 2016/08/29 717484895 1 2288 Normal Sale 339515 2016/08/29 717484895 1 2288 Normal Sale 447953 2016/08/29 717484895 1 2288 Normal Sale 474866 2016/08/29 717484895 1 2288 Normal Sale 824920 2016/08/29 717484895 1 2289 Normal Sale 220186 2016/08/29 717484895 1 2289 Normal Sale 340075 2016/08/29 717484895 1 2289 Normal Sale 756999 2016/08/29 717484895 1 2289 Normal Sale 904664 2016/08/29 717484895 1 2113 Normal Sale 88328 2016/08/29 717484895 1 2113 Normal Sale 316844 2016/08/29 717484895 1 2114 Normal Sale 994145 2016/08/29 717484895 1 2115 Normal Sale 54841 2016/08/29 717484895 1 2115 Normal Sale 893727 2016/08/29 717484895 1 2115 Normal Sale 929620 2016/08/29 717484895 1 2116 Normal Sale 201467 2016/08/29 717484895 1 2116 Normal Sale 850818 2016/08/29 717484895 1 2117 Normal Sale 2808 2016/08/29 717484895 1 2117 Normal Sale 271338 2016/08/29 717484895 1 2118 Normal Sale 205518 2016/08/29 717484895 1 2118 Normal Sale 438119 2016/08/29 717484895 1 2119 Normal Sale 240 2016/08/29 717484895 1 2119 Normal Sale 9118 2016/08/29 717484895 1 2119 Normal Sale 206201 2016/08/29 717484895 1 2119 Normal Sale 560698 2016/08/29 717484895 1 2120 Normal Sale 34033 2016/08/29 717484895 1 2120 Normal Sale 521468 2016/08/29 717484895 1 2121 Normal Sale 802 2016/08/29 717484895 1 2121 Normal Sale 129031 2016/08/29 717484895 1 2122 Normal Sale 151100 2016/08/29 717484895 1 2122 Normal Sale 154757 2016/08/29 717484895 1 2123 Normal Sale 535567 2016/08/29 717484895 1 2123 Normal Sale 867655 2016/08/29 717484895 1 2124 Normal Sale 931733 2016/08/29 717484895 1 2125 Normal Sale 231167 2016/08/29 717484895 1 2126 Normal Sale 342360 2016/08/29 717484895 1 2126 Normal Sale 630780 2016/08/29 717484895 1 2126 Normal Sale 921304 2016/08/29 717484895 1 2127 Normal Sale 181388 2016/08/29 717484895 1 2127 Normal Sale 576306 2016/08/29 717484895 1 2128 Normal Sale 129031 2016/08/29 717484895 1 2128 Normal Sale 591644 2016/08/29 717484895 1 2129 Normal Sale 18812 2016/08/29 717484895 1 2129 Normal Sale 124362 2016/08/29 717484895 1 2129 Normal Sale 389544 2016/08/29 717484895 1 2129 Normal Sale 681411 2016/08/29 717484895 1 2130 Normal Sale 550764 2016/08/29 717484895 1 2130 Normal Sale 673442 2016/08/29 717484895 1 2132 Normal Sale 19091 2016/08/29 717484895 1 2132 Normal Sale 261743 2016/08/29 717484895 1 2132 Normal Sale 771840 2016/08/29 717484895 1 2184 Normal Sale 100594 2016/08/29 717484895 1 2184 Normal Sale 312801 2016/08/29 717484895 1 2185 Normal Sale 3186 2016/08/29 717484895 1 2185 Normal Sale 891739 2016/08/29 717484895 1 2187 Normal Sale 240 2016/08/29 717484895 1 2187 Normal Sale 313908 2016/08/29 717484895 1 2188 Normal Sale 449959 2016/08/29 717484895 1 2189 Normal Sale 188474 2016/08/29 717484895 1 2189 Normal Sale 227918 2016/08/29 717484895 1 2189 Normal Sale 486639 2016/08/29 717484895 1 2189 Normal Sale 644336 2016/08/29 717484895 1 2191 Normal Sale 596 2016/08/29 717484895 1 2193 Normal Sale 154666 2016/08/29 717484895 1 2194 Normal Sale 48611 2016/08/29 717484895 1 2194 Normal Sale 99408 2016/08/29 717484895 1 2195 Normal Sale 53645 2016/08/29 717484895 1 2195 Normal Sale 143776 2016/08/29 717484895 1 2195 Normal Sale 298075 2016/08/29 717484895 1 2195 Normal Sale 618504 2016/08/29 717484895 1 2195 Normal Sale 627943 2016/08/29 717484895 1 2196 Normal Sale 334276 2016/08/29 717484895 1 2196 Normal Sale 395129 2016/08/29 717484895 1 2197 Normal Sale 224501 2016/08/29 7325852 1 2197 Normal Sale 685594 2016/08/29 7325852 1 2198 Normal Sale 3228 2016/08/29 7325852 1 2199 Normal Sale 224501 2016/08/29 7325852 1 2200 Normal Sale 482448 2016/08/29 7325852 1 2203 Normal Sale 132902 2016/08/29 7325852 1 2203 Normal Sale 241778 2016/08/29 7325852 1 2204 Normal Sale 179325 2016/08/29 7325852 1 2204 Normal Sale 566836 2016/08/29 7325852 1 2204 Normal Sale 619163 2016/08/29 7325852 1 2204 Normal Sale 791467 2016/08/29 7325852 1 2206 Normal Sale 496547 2016/08/29 7325852 1 2207 Normal Sale 255380 2016/08/29 7325852 1 2207 Normal Sale 618454 2016/08/29 7325852 1 2207 Normal Sale 778126 2016/08/29 7325852 1 2210 Normal Sale 271247 2016/08/29 7325852 1 2210 Normal Sale 771840 2016/08/29 7325852 1 2211 Normal Sale 698878 2016/08/29 7325852 1 2211 Normal Sale 779272
• ###### 1. Re: Help with calculation

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

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

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