Grouping issue

Question & Answer
,
1

HI Team,

I need your help.

I am getting different values when i group at Date and category level because of data. Like below we have positive and negative values at record level.

The correct total is the last column as per customer. How do we match both as per Date and category.

Values
Row Labels Sum of sales Abs ( at Record Level ) abs ( Column B )
20230101 -2486137.96 2723310.14 2486137.96
20230201 -2122903.66 2300504.88 2122903.66
20230301 -3079402.53 3123452.05 3079402.53
20230401 -2025584.01 2131897.47 2025584.01
20230501 1973712.45 3455549.23 1973712.45
(blank)
Grand Total -7740315.71 13734713.77 11687740.61
Values
Row Labels Sum of sales Abs ( at Record Level ) abs ( Column B )
Category 1 -1136873.86 5329967.54 1136873.86
Category 2 -5597630.63 5810079.15 5597630.63
Category 3 -688680.17 2229577.29 688680.17
Category 4 -317131.05 365089.79 317131.05
(blank)
Grand Total -7740315.71 13734713.77 7740315.71
2

You can make a calculated field that will group it how you want.

sum({sales},[{customer}])

3

Thanks Max,

I tried with both the calculations and filter only 2023 data.

Below i gave the sample data.

Grouping by date i am getting = 11,687,740.61 but i am not getting the same number when i do with group level.

Please help .

sumOver(
abs(
sum(
ifelse
(
dateDiff(truncDate(“YYYY”, date), truncDate(“YYYY”, now()), ‘YYYY’) = 0,
{total_revenue},0
)
)
),
[date]
)

sumOver(
abs(
sum(
ifelse
(
dateDiff(truncDate(“YYYY”, date), truncDate(“YYYY”, now()), ‘YYYY’) = 0,
{total_revenue},0
)
)
),
[{Revenue Group}]
)

revenue_group datenum_key total_revenue
Category 1 20230501 400.87
Category 1 20230101 -7781.25
Category 1 20230301 0
Category 2 20230101 0
Category 2 20230401 0
Category 2 20230401 -5366.67
Category 1 20230101 -1750
Category 1 20230101 -5796.43
Category 1 20230401 -3477.85
Category 1 20230301 -2333.33
Category 1 20230201 -15000
Category 2 20230101 -12949.09
Category 2 20230201 0
Category 2 20230401 -7655.88
Category 3 20230201 0
Category 3 20230201 -2085
Category 3 20230101 -1000
Category 3 20230501 -22575.45
Category 3 20230101 -5946.08
Category 3 20230101 3750
Category 3 20230401 -17220
Category 3 20230101 -7347.37
Category 3 20230101 0
Category 3 20230301 0
Category 3 20230301 -2589.04
Category 3 20230301 -5178.57
Category 3 20230301 0
Category 3 20230201 -18750
Category 3 20230101 0
Category 3 20230401 -14285.72
Category 3 20230301 -14285.71
Category 3 20230301 2423.08
Category 3 20230101 -5787.69
Category 3 20230401 -6131.39
Category 2 20230401 -21850
Category 2 20230201 -1582.55
Category 2 20230101 91000
Category 1 20230501 11018.38
Category 4 20230401 -2166
Category 4 20230301 -3512.85
Category 4 20230101 -3716.1
Category 4 20230301 -2400.9
Category 4 20230301 -50000
Category 4 20230501 -3284.25
Category 4 20230201 -2707.73
Category 4 20230301 -11271.65
Category 4 20230201 -816.06
Category 4 20230101 -337.5
Category 4 20230101 -8190
Category 4 20230501 -8820
Category 3 20230301 -5585
Category 3 20230501 -84.5
Category 3 20230301 -9243.13
Category 3 20230201 -6628.57
Category 3 20230401 -5600
Category 3 20230401 -19600
Category 3 20230101 0
Category 4 20230301 -8618.4
Category 4 20230301 -1411.98
Category 4 20230301 -2200
Category 4 20230101 0
Category 4 20230401 -514142.26
Category 3 20230101 -171.68
Category 3 20230401 -10028.78
Category 3 20230301 -5833.33
Category 3 20230501 232831.5
Category 3 20230101 0
Category 3 20230301 -2000
Category 3 20230201 0
Category 3 20230101 -37200
Category 4 20230101 -1501.73
Category 4 20230401 -1893.28
Category 4 20230201 -307376.15
Category 4 20230101 -307073.15
Category 4 20230301 -4556
Category 4 20230501 -4802.55
Category 4 20230301 -805.53
Category 4 20230101 -3300
Category 4 20230201 -1527.7
Category 4 20230301 -14583.33
Category 4 20230201 -2014.92
Category 4 20230401 -9558.59
Category 4 20230101 2514.87
Category 4 20230301 -1380
Category 4 20230101 -747.5
Category 4 20230201 -147
Category 1 20230101 -3154.29
Category 2 20230201 -36222.97
Category 2 20230401 -73550
Category 2 20230201 -2683.33
Category 2 20230101 -2307.69
Category 3 20230301 -1749.1
Category 3 20230401 -7131.6
Category 3 20230301 -3272.73
Category 3 20230201 0
Category 3 20230201 -540
Category 3 20230101 -91000
Category 3 20230301 0
Category 3 20230101 -5229
Category 2 20230301 -2940
Category 2 20230301 -12500
Category 1 20230101 0
Category 4 20230401 -290.82
Category 4 20230401 -3637.05
Category 4 20230501 -842.75
Category 4 20230401 -3116.5
Category 4 20230501 -2235.91
Category 4 20230501 -12776.94
Category 4 20230401 -8235
Category 4 20230401 -8960
Category 4 20230201 -9730
Category 3 20230401 0
Category 3 20230301 0
Category 3 20230201 -1110.75
Category 3 20230501 -4000
Category 3 20230301 -8538.7
Category 3 20230101 -11536.2
Category 3 20230201 -16810.51
Category 3 20230201 -2800
Category 3 20230401 -15000
Category 3 20230401 0
Category 3 20230301 -11900
Category 3 20230501 -10500
Category 3 20230101 -2075
Category 3 20230301 0
Category 3 20230201 0
Category 4 20230401 -11.6
Category 4 20230201 -4057.65
Category 4 20230401 -4309.2
Category 4 20230401 -221.58
Category 4 20230301 -4970
Category 4 20230301 -11462.5
Category 4 20230201 -2200
Category 4 20230401 -2300
Category 4 20230201 -1556.5
Category 3 20230101 -4000
Category 3 20230101 0
Category 3 20230201 0
Category 3 20230201 -36000
Category 3 20230301 -12642.86
Category 3 20230101 -2566.5
Category 4 20230201 -1735.64
Category 4 20230301 -2233.53
Category 4 20230201 -38257.23
Category 4 20230501 -34400.69
Category 4 20230201 -1077.06
Category 4 20230501 -759
Category 4 20230501 -1567.99
Category 4 20230301 -1034.08
Category 4 20230101 -4680
Category 4 20230301 -5198.78
Category 4 20230401 -4683.14
Category 4 20230401 -1711.6
Category 4 20230301 -6456.45
Category 4 20230101 -6550
Category 4 20230201 -68178.88
Category 4 20230301 -67736.36
Category 4 20230101 -120
Category 4 20230101 -8402.16
Category 4 20230401 -2968.44
Category 1 20230201 -3154.28
Category 1 20230301 -788.57
Category 1 20230101 0
Category 2 20230401 -5634.25
Category 2 20230301 -1052.94
Category 2 20230301 -2500
Category 2 20230501 180000
Category 2 20230501 71900
Category 2 20230301 -3220
Category 1 20230401 -83.33
Category 2 20230501 298973.9
Category 2 20230101 -409552.9
Category 2 20230401 30000
Category 2 20230101 0
Category 3 20230401 -13591.55
Category 3 20230501 24375
Category 3 20230301 -27490
Category 3 20230401 -250
Category 3 20230501 133500
Category 3 20230401 0
Category 3 20230401 -1260
Category 3 20230101 0
Category 3 20230401 0
Category 3 20230101 -80000
Category 3 20230201 42857.14
Category 3 20230401 -25000
Category 3 20230501 6131.39
Category 2 20230501 5146.77
Category 2 20230101 8444.21
Category 2 20230301 -13636.36
Category 1 20230301 -4583.33
Category 2 20230101 -1785
Category 2 20230101 -15301.33
Category 1 20230401 -5955.88
Category 4 20230301 -2875
Category 4 20230101 -3099.6
Category 4 20230301 -5560
Category 4 20230301 814.07
Category 4 20230201 -2928.49
Category 4 20230301 -1331.53
Category 4 20230301 -5110.6
Category 3 20230201 -16500
Category 3 20230301 -21600
Category 3 20230301 -16810.5
Category 3 20230201 -10000
Category 3 20230301 -20191.55
Category 3 20230301 -62800
Category 3 20230401 0
Category 4 20230301 -21
Category 4 20230201 -5180
Category 4 20230401 -4450
Category 4 20230301 -8375
Category 4 20230301 -4950.57
Category 3 20230301 -6165.33
Category 3 20230401 -36874.99
Category 3 20230101 -6720
Category 3 20230401 -6800
Category 3 20230201 -8095
Category 3 20230101 -8018.18
Category 3 20230201 0
Category 3 20230301 0
Category 3 20230301 -6250
Category 3 20230101 -35160
Category 3 20230201 -9830
Category 3 20230101 -1687.5
Category 4 20230401 -49744.9
Category 4 20230101 -3399.04
Category 4 20230301 -11270.85
Category 4 20230101 -14583.33
Category 4 20230301 -4425
Category 4 20230101 -20543
Category 4 20230101 -1368
Category 4 20230301 -1274.12
Category 4 20230101 -47.05
Category 4 20230301 -4115.1
Category 4 20230401 -6500
Category 4 20230401 -68178.88
Category 4 20230301 -405
Category 4 20230101 -181.27
Category 1 20230201 -22578.22
Category 1 20230101 -11953.57
Category 2 20230401 0
Category 2 20230301 -91800
Category 2 20230201 -15950
Category 2 20230401 -6479.62
Category 3 20230201 -4607.51
Category 3 20230101 -11600
Category 3 20230301 -44517.62
Category 3 20230201 -31495.78
Category 3 20230101 -30000
Category 3 20230201 2286.8
Category 3 20230401 -30000
Category 3 20230101 -26010
Category 3 20230101 -3000
Category 3 20230201 -5178.57
Category 3 20230201 0
Category 3 20230101 0
Category 3 20230301 -7163.08
Category 2 20230501 6666.67
Category 2 20230101 -8312.5
Category 2 20230301 -43700
Category 2 20230201 -6985
Category 2 20230401 -75000
Category 2 20230401 -5334.93
Category 2 20230301 -1517.49
Category 1 20230201 -11851.1
Category 4 20230301 -1650.25
Category 4 20230201 -10468.75
Category 4 20230501 -3713.4
Category 4 20230301 -1556.5
Category 4 20230501 -4131
Category 4 20230501 -690
Category 4 20230301 -11000
Category 4 20230201 -9100
Category 4 20230301 -8899.03
Category 3 20230401 -9900
Category 3 20230201 0
Category 3 20230301 0
Category 3 20230201 -33657.65
Category 3 20230101 -1117
Category 3 20230401 0
Category 3 20230101 -1320.83
Category 3 20230301 -2000
Category 3 20230201 12666.67
Category 3 20230101 -6628.57
Category 3 20230301 -13257.15
Category 3 20230201 -20191.55
Category 4 20230401 -2780.58
Category 4 20230301 -6419.85
Category 4 20230101 -4309.2
Category 4 20230401 -4970
Category 4 20230101 -5000
Category 4 20230501 -7987.5
Category 4 20230201 -8625
Category 3 20230401 0
Category 3 20230301 -11250
Category 3 20230301 -55000
Category 3 20230201 0
Category 3 20230201 0
Category 3 20230501 490500
Category 3 20230301 -684000
Category 3 20230101 -500
Category 3 20230401 0
Category 3 20230201 -15187.5
Category 3 20230301 -3375
Category 4 20230401 2000
Category 4 20230101 4000
Category 4 20230101 -1613.02
Category 4 20230301 -306476.76
Category 4 20230101 -6658.89
Category 4 20230201 -14583.33
Category 4 20230401 -1786.67
Category 4 20230401 -2145
Category 4 20230401 -1556.4
Category 4 20230101 -389
Category 4 20230301 -144.3
Category 4 20230401 -2953.8
Category 4 20230201 -1711.6
Category 4 20230501 -6350
Category 4 20230501 -500
Category 4 20230101 -1500
Category 4 20230201 -25546.28
Category 4 20230401 -42133.44
Category 4 20230301 -2026.97
Category 1 20230401 -32083.34
Category 2 20230101 0
Category 2 20230501 0
Category 2 20230201 0
Category 2 20230301 0
Category 2 20230301 -29102.5
Category 2 20230101 -5873.68
Category 1 20230501 2333.33
Category 2 20230401 -7300
Category 2 20230301 0
Category 3 20230301 0
Category 3 20230101 -10964.12
Category 3 20230201 -24791.47
Category 3 20230101 -17661.12
Category 3 20230201 -13393.33
Category 3 20230201 -220000
Category 3 20230201 -23000
Category 3 20230201 0
Category 3 20230201 -12923.08
Category 3 20230201 -2387.69
Category 2 20230401 -2931.58
Category 2 20230301 0
Category 1 20230501 105.77
Category 1 20230201 -18750
Category 1 20230101 -6652.94
Category 2 20230301 -5333.33
Category 1 20230101 -4156.25
Category 4 20230401 -720
Category 4 20230401 -966.9
Category 4 20230101 -3962.25
Category 4 20230401 -2245.89
Category 4 20230101 -2270.69
Category 4 20230401 -13973.52
Category 4 20230201 -3156.6
Category 4 20230301 -1007.13
Category 3 20230401 -470
Category 3 20230401 -13204.46
Category 3 20230401 -5276.29
Category 3 20230201 -320
Category 3 20230101 -2258.33
Category 3 20230201 -2375
Category 3 20230201 -8000
Category 4 20230301 -424.54
Category 4 20230401 -9362.55
Category 4 20230201 -4309.2
Category 4 20230101 -2200
Category 4 20230201 -32245.48
Category 4 20230201 0
Category 4 20230201 -566157.06
Category 3 20230501 25000
Category 3 20230401 0
Category 3 20230101 0
Category 3 20230301 0
Category 3 20230501 437000
Category 3 20230101 -0.01
Category 3 20230201 0
Category 3 20230201 -8018.19
Category 3 20230301 0
Category 3 20230101 -34790.54
Category 3 20230501 -11740
Category 3 20230301 -11500
Category 4 20230301 -9966.3
Category 4 20230401 -7935.9
Category 4 20230201 -5189.97
Category 4 20230101 -4901.34
Category 4 20230301 -6874.24
Category 4 20230501 -6881.79
Category 4 20230201 -6711.79
Category 4 20230201 -3259
Category 4 20230401 -780
Category 4 20230201 -1575
Category 4 20230201 -3932.39
Category 4 20230201 -5873.4
Category 4 20230401 -3285.88
Category 4 20230401 -5381.5
Category 4 20230301 -628.71
Category 4 20230101 -2974
Category 4 20230201 24990
Category 4 20230301 -293
Category 1 20230401 -2548.37
Category 1 20230201 -7781.25
Category 2 20230301 -7512.33
Category 2 20230201 -3756.17
Category 2 20230501 -4500
Category 2 20230101 7707.01
Category 2 20230101 -5366.67
Category 2 20230401 -3850
Category 1 20230201 -2277.66
Category 2 20230101 1170
Category 2 20230501 -3610
Category 2 20230401 0
Category 2 20230201 0
Category 2 20230501 14220
Category 2 20230101 -13200
Category 3 20230101 0
Category 3 20230401 0
Category 3 20230501 -14220
Category 3 20230301 -8058.12
Category 3 20230301 -71099.17
Category 3 20230401 -6250.74
Category 3 20230301 -30240
Category 3 20230101 -33657.54
Category 3 20230101 -339.56
Category 3 20230401 -7163.08
Category 2 20230101 -14000
Category 2 20230501 -52500
Category 2 20230101 -3086.21
Category 2 20230101 -9090.91
Category 2 20230501 -5700
Category 2 20230201 -56000
Category 1 20230201 -29000
Category 2 20230401 -5333.34
Category 2 20230301 -1785
Category 2 20230401 -1785
Category 2 20230501 0
Category 1 20230301 2382.35
Category 1 20230301 -15400
Category 4 20230501 -527.99
Category 4 20230101 -20025
Category 4 20230301 -1041.5
Category 4 20230401 -30606.13
Category 4 20230101 -11033.05
Category 4 20230101 -4160
Category 4 20230501 -5215.16
Category 4 20230101 -9100
Category 4 20230401 -3542.7
Category 4 20230401 -300
Category 3 20230201 -9900
Category 3 20230501 16920
Category 3 20230401 -16277.27
Category 3 20230301 -445
Category 3 20230201 -22467.3
Category 3 20230101 -3016.67
Category 3 20230501 175000
Category 4 20230201 -3475
Category 4 20230101 -11875
Category 4 20230501 -689.33
Category 4 20230501 35021.99
Category 3 20230301 -10311
Category 3 20230101 0
Category 3 20230401 0
Category 3 20230401 -3570
Category 3 20230101 0
Category 3 20230301 0
Category 3 20230101 -2542.5
Category 4 20230201 6000
Category 4 20230301 8000
Category 4 20230501 -302241.15
Category 4 20230401 -307610.15
Category 4 20230401 -4516.84
Category 4 20230101 -6667.5
Category 4 20230201 -6825
Category 4 20230501 -739.9
Category 4 20230101 -2065.31
Category 4 20230501 -10022.3
Category 4 20230101 -686.99
Category 4 20230401 -18083.33
Category 4 20230101 -3872.39
Category 4 20230501 -2006
Category 4 20230201 -3028.66
Category 4 20230301 -988.68
Category 4 20230301 -4266.03
Category 4 20230101 -253.2
Category 4 20230101 -65071.48
Category 4 20230301 -64049.11
Category 4 20230201 -4110.13
Category 4 20230501 -2514.77
Category 1 20230401 -788.57
Category 1 20230401 -3869.44
Category 1 20230501 3406.94
Category 2 20230301 0
Category 2 20230201 0
Category 2 20230401 -45659.65
Category 2 20230101 -12960
Category 2 20230301 -33635
Category 2 20230201 -3220
Category 1 20230401 -27500
Category 1 20230201 -1159.29
Category 1 20230201 -2416.67
Category 2 20230101 -2045.45
Category 2 20230201 0
Category 2 20230301 0
Category 2 20230101 0
Category 3 20230101 -4320.41
Category 3 20230101 -12562.5
Category 3 20230301 8405.26
Category 3 20230101 -2450
Category 3 20230301 -9612.5
Category 3 20230501 30000
Category 3 20230301 0
Category 3 20230101 -5178.57
Category 3 20230401 -18000
Category 3 20230401 0
Category 3 20230201 -25000
Category 2 20230401 -1325
Category 2 20230501 55220
Category 2 20230101 -5020
Category 2 20230401 -50200
Category 1 20230401 4331.73
Category 1 20230101 -12000
Category 2 20230101 -8846.6
Category 1 20230501 -4000
Category 1 20230201 -5500
Category 4 20230201 -3300
Category 4 20230501 -899.64
Category 4 20230301 -2273.39
Category 4 20230101 -40718.73
Category 4 20230201 -13653.3
Category 4 20230501 0
Category 4 20230201 -9440
Category 4 20230301 -5955
Category 4 20230101 -1636.02
Category 4 20230401 -12100
Category 3 20230101 0
Category 3 20230301 -19514.02
Category 3 20230201 0
Category 3 20230101 0
Category 3 20230101 -1516
Category 3 20230401 -13257.14
Category 3 20230501 -2608.14
Category 3 20230101 -12960.9
Category 3 20230101 -22091.55
Category 3 20230401 -18290.55
Category 3 20230101 0
Category 3 20230101 -21000
Category 4 20230301 -5561.16
Category 4 20230501 2058.33
Category 4 20230101 -29920.17
Category 3 20230401 -3082.67
Category 3 20230301 0
Category 3 20230501 119900
Category 3 20230401 -25234.53
Category 3 20230101 -10833.33
Category 3 20230401 0
Category 3 20230501 -7000
Category 3 20230501 187500
Category 4 20230101 -118967.1
Category 4 20230401 -893.98
Category 4 20230301 -2975.72
Category 4 20230401 -1825.05
Category 4 20230201 -3650.1
Category 4 20230501 -456.36
Category 4 20230401 -2550
Category 4 20230301 -2589.02
Category 4 20230201 -612.16
Category 4 20230301 -29337.88
Category 4 20230301 -1711.6
Category 4 20230201 -3113
Category 4 20230201 -6500
Category 4 20230501 -500
Category 4 20230401 -1203.32
Category 4 20230301 -3989.96
Category 4 20230501 -2521.11
Category 1 20230201 0
Category 1 20230301 0
Category 1 20230201 -24583.34
Category 2 20230101 0
Category 2 20230201 -2105.88
Category 2 20230301 -2160
Category 2 20230301 -8050
Category 1 20230301 -9110.67
Category 1 20230301 -3477.86
Category 1 20230501 -15000
Category 2 20230401 -3580
Category 2 20230301 -61247.06
Category 2 20230301 -14220
Category 2 20230201 -31153.85
Category 3 20230301 -34962.96
Category 3 20230401 -6030
Category 3 20230401 -20108.75
Category 3 20230201 -895
Category 3 20230201 -11454.54
Category 3 20230101 -2341.7
Category 3 20230101 -20625
Category 3 20230301 -900
Category 3 20230301 -5400
Category 3 20230301 -270
Category 3 20230501 -71900
Category 3 20230401 -825
Category 3 20230501 45500
Category 3 20230401 -15301.78
Category 3 20230401 -1750
Category 2 20230301 -8794.74
Category 2 20230201 -2246.97
Category 2 20230201 -5020
Category 1 20230401 -2813.34
Category 2 20230201 -2948.87
Category 2 20230201 -25702.44
Category 1 20230101 -10000
Category 4 20230401 -10512.5
Category 4 20230201 -3544.8
Category 4 20230201 -4612.5
Category 4 20230201 -2398.68
Category 4 20230101 -1809.45
Category 4 20230201 -660
Category 4 20230401 -3037.03
Category 4 20230501 -5520.5
Category 3 20230501 -2999
Category 3 20230101 -20400
Category 3 20230201 -9243.12
Category 3 20230101 -9243.13
Category 3 20230401 -9169.37
Category 3 20230301 -5600
Category 3 20230301 -100000
Category 3 20230501 100000
Category 3 20230401 -13900
Category 4 20230201 -2780.58
Category 4 20230101 -5180
Category 4 20230401 -6420.92
Category 4 20230401 -20039.16
Category 4 20230101 -577534.04
Category 4 20230301 -542617.95
Category 3 20230201 -9248
Category 3 20230201 0
Category 3 20230401 -7500
Category 3 20230401 0
Category 3 20230401 -8909.09
Category 3 20230401 -1687.5
Category 4 20230501 4000
Category 4 20230301 -73445.57
Category 4 20230501 -1234.06
Category 4 20230401 -1329.41
Category 4 20230201 -5549.55
Category 4 20230401 -6622.18
Category 4 20230401 -6150.96
Category 4 20230401 -1075.35
Category 4 20230301 -709.35
Category 4 20230501 -2263.51
Category 4 20230401 -3480
Category 4 20230501 -1711.6
Category 4 20230401 -896.57
Category 4 20230501 -66862.72
Category 4 20230101 -39708.98
Category 4 20230401 16825
Category 4 20230101 -1503.98
Category 4 20230501 -2311.43
4

Hi Max,

Any help on the scenario.

5

Hi @sasikanth

What are you looking to do?

Only sum when it’s positive?

How should you data look like given this example?

6

@Max ,

I got the data like above 3 columns.

we have to do sum then do the ABS to match.

When i use the above formula - i got different results. My question is how do we get the same number when we do gruping by date, and revenue group.

7

You can try to do sumOver with a PRE_AGG and then check your abs match.

sumOver({sales},[{customer}],PRE_AGG)

Then do logic with this ^