Annual accumulated sales amount by year, month and category in the Data load editor

SMalli — Tue, 26 Jul 2022 00:17:12 GMT

Hello,

I've sample data and I would like to calculate accumulated sales amount by year, month and category .

the calculation needs to be performed in the backend.

I've attached the sample data along with the qvf

Re: Calculate annual accumulated sales amount by year, month and category in the Data load editor

brunobertels — Tue, 19 Jul 2022 08:03:04 GMT

May be like this

1/ load data from excel or from load inline

2/ add row ID , foramt year and month order by categorie year month

3/ add cumulative sum

4/ drop table :

[Sample]:

LOAD * INLINE
[
Year,Month,CategoryName,Gross Sales
2008,Jan,Sportswear,38.88
2008,Jan,Swimwear,153.4
2008,Jan,Baby Clothes,183.45
2008,Jan,Baby Clothes,195.3
2008,Jan,Men's Clothes,273.3
2008,Jan,Sportswear,355.2
2008,Jan,Sportswear,471.04
2008,Jan,Swimwear,606
2008,Jan,Baby Clothes,773.55
2008,Jan,Sportswear,1211.2
2008,Jan,-,-
2008,Feb,Sportswear,28.35
2008,Feb,Women's Clothes,50.56
2008,Feb,Sportswear,280.8
2008,Feb,Men's Clothes,319.44
2008,Feb,Men's Footwear,1173
2008,Feb,-,-
2008,Mar,Baby Clothes,26.75
2008,Mar,Sportswear,128.55
2008,Mar,Women's Clothes,168.16
2008,Mar,Men's Footwear,213.12
2008,Mar,Baby Clothes,460.6
2008,Mar,Women's Clothes,1292.48
2008,Mar,Women's Clothes,1984.32
2008,Mar,-,-
2008,Apr,Sportswear,225.4
2008,Apr,Sportswear,274.5
2008,Apr,Baby Clothes,293.6
2008,Apr,Sportswear,420
2008,Apr,Women's Clothes,596.7
2008,Apr,Men's Clothes,701.6
2008,Apr,-,-
2008,May,Baby Clothes,103.8
2008,May,Women's Clothes,213.2
2008,May,Baby Clothes,274.5
2008,May,Baby Clothes,381
2008,May,Men's Footwear,524.8
2008,May,Women's Clothes,727.3
2008,May,Sportswear,857.08
2008,May,Women's Clothes,1358
2008,May,Men's Footwear,1712.2
2008,May,-,-
2008,Jun,Women's Clothes,45.6
2008,Jun,Men's Clothes,46.5
2008,Jun,Baby Clothes,46.62
2008,Jun,Sportswear,52.65
2008,Jun,Sportswear,61.5
2008,Jun,Women's Clothes,63.55
2008,Jun,Baby Clothes,68
2008,Jun,Women's Clothes,100.8
2008,Jun,Sportswear,132.1
2008,Jun,Baby Clothes,135.46
2008,Jun,Men's Clothes,148.65
2008,Jun,Women's Footwear,451.25
2008,Jun,Children's Clothes,617.11
2008,Jun,Men's Clothes,707.52
2008,Jun,Women's Footwear,709.56
2008,Jun,Sportswear,752
2008,Jun,Sportswear,889.68
2008,Jun,Women's Footwear,1131.9
2008,Jun,Women's Clothes,1500.8
2008,Jun,Men's Footwear,2195.8
2008,Jun,Men's Footwear,2355.6
2008,Jun,Women's Clothes,2936
2008,Jun,Women's Clothes,5677.98
2008,Jun,-,-
2008,Jul,Women's Clothes,30.6
2008,Jul,Sportswear,32.94
2008,Jul,Men's Clothes,51
2008,Jul,Sportswear,57.36
2008,Jul,Baby Clothes,74.64
2008,Jul,Sportswear,79.8
2008,Jul,Sportswear,85
2008,Jul,Men's Clothes,179.84
2008,Jul,Baby Clothes,196.88
2008,Jul,Women's Clothes,200
2008,Jul,Men's Clothes,216.3
2008,Jul,Baby Clothes,241.02
2008,Jul,Women's Clothes,244.84
2008,Jul,Swimwear,247.35
2008,Jul,Children's Clothes,323.85
2008,Jul,Women's Clothes,325.6
2008,Jul,Sportswear,329.5
2008,Jul,Children's Clothes,428.26
2008,Jul,Men's Clothes,480.5
2008,Jul,Women's Footwear,481.56
2008,Jul,Sportswear,577.44
2008,Jul,Children's Clothes,823.6
2008,Jul,Baby Clothes,840.7
2008,Jul,Sportswear,849.4
2008,Jul,Women's Clothes,1195.6
2008,Jul,Baby Clothes,1319.1
2008,Jul,Children's Clothes,1360.5
2008,Jul,Men's Clothes,1455
2008,Jul,Women's Clothes,1547.2
2008,Jul,Sportswear,1649.4
2008,Jul,Men's Footwear,1811.32
2008,Jul,Sportswear,2422.28
2008,Jul,-,-
2008,Aug,Baby Clothes,14.98
2008,Aug,Sportswear,31.65
2008,Aug,Men's Footwear,45.34
2008,Aug,Men's Clothes,46.2
2008,Aug,Sportswear,46.2
2008,Aug,Sportswear,56.66
2008,Aug,Sportswear,149.12
2008,Aug,Sportswear,158.4
2008,Aug,Baby Clothes,272.5
2008,Aug,Sportswear,300.4
2008,Aug,Men's Footwear,402.24
2008,Aug,Men's Clothes,484.95
2008,Aug,Sportswear,513.45
2008,Aug,Sportswear,516.8
2008,Aug,Men's Clothes,765.6
2008,Aug,Swimwear,780
2008,Aug,Sportswear,886.25
2008,Aug,Women's Clothes,924
2008,Aug,Sportswear,1272.04
2008,Aug,Sportswear,1290.5
2008,Aug,Sportswear,1364.8
2008,Aug,Women's Footwear,1697.5
2008,Aug,Women's Clothes,5862.3
2008,Aug,-,-
2008,Sep,Women's Footwear,45
2008,Sep,Baby Clothes,97.86
2008,Sep,Baby Clothes,104.64
2008,Sep,Women's Clothes,126
2008,Sep,Men's Clothes,200.5
2008,Sep,Sportswear,701.28
2008,Sep,Men's Footwear,809.7
2008,Sep,Baby Clothes,895
2008,Sep,Sportswear,1044.4
2008,Sep,Men's Footwear,1268.5
2008,Sep,Women's Footwear,1759.6
2008,Sep,-,-
2008,Oct,Sportswear,18.7
2008,Oct,Baby Clothes,24.2
2008,Oct,Men's Clothes,24.81
2008,Oct,Sportswear,36.56
2008,Oct,Sportswear,48.45
2008,Oct,Baby Clothes,59.25
2008,Oct,Sportswear,75.28
2008,Oct,Men's Clothes,84.2
2008,Oct,Sportswear,97.68
2008,Oct,Women's Clothes,99.4
2008,Oct,Sportswear,111.12
2008,Oct,Women's Clothes,120.3
2008,Oct,Men's Clothes,128.8
2008,Oct,Women's Footwear,133.65
2008,Oct,Women's Clothes,136.36
2008,Oct,Women's Clothes,142.35
2008,Oct,Men's Clothes,160.26
2008,Oct,Sportswear,161.52
2008,Oct,Baby Clothes,175.9
2008,Oct,Sportswear,195.18
2008,Oct,Women's Clothes,199.3
2008,Oct,Men's Clothes,257.3
2008,Oct,Sportswear,264
2008,Oct,Women's Footwear,276.4
2008,Oct,Women's Footwear,291.12
2008,Oct,Men's Clothes,313.68
2008,Oct,Sportswear,319.08
2008,Oct,Sportswear,334.6
2008,Oct,Men's Clothes,335.2
2008,Oct,Baby Clothes,373
2008,Oct,Men's Clothes,391.44
2008,Oct,Sportswear,452.76
2008,Oct,Sportswear,493.2
2008,Oct,Children's Clothes,522.24
2008,Oct,Men's Clothes,702.4
2008,Oct,Baby Clothes,755.7
2008,Oct,Men's Footwear,780.3
2008,Oct,Women's Footwear,796.4
2008,Oct,Sportswear,806.64
2008,Oct,Sportswear,826.8
2008,Oct,Women's Clothes,838.5
2008,Oct,Men's Clothes,909.9
2008,Oct,Men's Clothes,919.2
2008,Oct,Sportswear,955.8
2008,Oct,Sportswear,1002
2008,Oct,Women's Footwear,1044.9
2008,Oct,Women's Footwear,1061.1
2008,Oct,Women's Footwear,1072.68
2008,Oct,Women's Footwear,1151.4
2008,Oct,Women's Clothes,1168.8
2008,Oct,Children's Clothes,1192.2
2008,Oct,Women's Clothes,1235.1
2008,Oct,Men's Footwear,1632.42
2008,Oct,Women's Clothes,1833.16
2008,Oct,Sportswear,2212.5
2008,Oct,Sportswear,2335.5
2008,Oct,Women's Clothes,2492.1
2008,Oct,Sportswear,2913.6
2008,Oct,Men's Footwear,3099.6
2008,Oct,-,-
2008,Nov,Baby Clothes,27.09
2008,Nov,Men's Clothes,31.68
2008,Nov,Swimwear,37.5
2008,Nov,Swimwear,45.6
2008,Nov,Women's Footwear,78.72
2008,Nov,Sportswear,85.3
2008,Nov,Swimwear,113.2
2008,Nov,Baby Clothes,119.7
2008,Nov,Sportswear,127.82
2008,Nov,Swimwear,129.84
2008,Nov,Sportswear,131.52
2008,Nov,Sportswear,160.95
2008,Nov,Sportswear,177.6
2008,Nov,Women's Footwear,178.5
2008,Nov,Swimwear,181.8
2008,Nov,Women's Clothes,243.4
2008,Nov,Men's Clothes,248.1
2008,Nov,Sportswear,248.43
2008,Nov,Men's Footwear,270
2008,Nov,Baby Clothes,275.7
2008,Nov,Sportswear,282.6
2008,Nov,Children's Clothes,294.96
2008,Nov,Swimwear,312
2008,Nov,Men's Clothes,323.4
2008,Nov,Sportswear,340.4
2008,Nov,Men's Clothes,362.4
2008,Nov,Sportswear,367.6
2008,Nov,Sportswear,421.68
2008,Nov,Children's Clothes,434.6
2008,Nov,Men's Clothes,454.35
2008,Nov,Sportswear,456.4
2008,Nov,Sportswear,464.55
2008,Nov,Women's Footwear,534.8
2008,Nov,Men's Footwear,538.5
2008,Nov,Women's Footwear,544.2
2008,Nov,Sportswear,557.4
2008,Nov,Sportswear,594.6
2008,Nov,Children's Clothes,652.8
2008,Nov,Sportswear,668.75
2008,Nov,Women's Footwear,672
2008,Nov,Swimwear,686
2008,Nov,Children's Clothes,736.35
2008,Nov,Men's Clothes,760.75
2008,Nov,Women's Clothes,761.25
2008,Nov,Men's Clothes,835.5
2008,Nov,Sportswear,1017.9
2008,Nov,Women's Footwear,1020.6
2008,Nov,Men's Footwear,1026
2008,Nov,Women's Clothes,1146
2008,Nov,Sportswear,1180.4
2008,Nov,Children's Clothes,1198.68
2008,Nov,Women's Clothes,1231.1
2008,Nov,Women's Clothes,1297.8
2008,Nov,Sportswear,1368.4
2008,Nov,Sportswear,1644
2008,Nov,Women's Clothes,2527.2
2008,Nov,Women's Clothes,2739.6
2008,Nov,Women's Clothes,3115.5
2008,Nov,Women's Clothes,8091
2008,Nov,-,-
2008,Dec,Baby Clothes,9.36
2008,Dec,Swimwear,18.72
2008,Dec,Women's Clothes,22.26
2008,Dec,Men's Clothes,31.2
2008,Dec,Men's Clothes,68.6
2008,Dec,Men's Clothes,73.8
2008,Dec,Sportswear,76.68
2008,Dec,Men's Clothes,88.11
2008,Dec,Baby Clothes,90
2008,Dec,Women's Clothes,95.94
2008,Dec,Baby Clothes,96.3
2008,Dec,Men's Clothes,110.25
2008,Dec,Swimwear,153.3
2008,Dec,Sportswear,159.68
2008,Dec,Children's Clothes,190.26
2008,Dec,Swimwear,203
2008,Dec,Women's Footwear,210.12
2008,Dec,Sportswear,217.84
2008,Dec,Women's Clothes,233.52
2008,Dec,Men's Clothes,240
2008,Dec,Baby Clothes,277.08
2008,Dec,Women's Clothes,281.28
2008,Dec,Women's Clothes,282.24
2008,Dec,Sportswear,297.5
2008,Dec,Men's Footwear,308.7
2008,Dec,Men's Clothes,312.4
2008,Dec,Sportswear,322.4
2008,Dec,Men's Footwear,324.06
2008,Dec,Swimwear,378.25
2008,Dec,Baby Clothes,387
2008,Dec,Sportswear,434
2008,Dec,Women's Clothes,438.4
2008,Dec,Sportswear,448.16
2008,Dec,Sportswear,449.4
2008,Dec,Women's Footwear,453
2008,Dec,Baby Clothes,456.25
2008,Dec,Women's Clothes,502.32
2008,Dec,Baby Clothes,517.6
2008,Dec,Men's Clothes,520.2
2008,Dec,Sportswear,529.5
2008,Dec,Men's Footwear,552.84
2008,Dec,Children's Clothes,643.8
2008,Dec,Sportswear,646.5
2008,Dec,Men's Footwear,672.8
2008,Dec,Men's Clothes,865.2
2008,Dec,Children's Clothes,985.5
2008,Dec,Baby Clothes,1043.4
2008,Dec,Women's Footwear,1596
2008,Dec,Men's Footwear,2034.2
2008,Dec,Men's Footwear,2406.24
2008,Dec,Men's Footwear,2554.65
2008,Dec,Sportswear,5004
2008,Dec,-,-
2009,Jan,Sportswear,94.08
2009,Jan,Men's Clothes,107.66
2009,Jan,Sportswear,129
2009,Jan,Sportswear,178.4
2009,Jan,Women's Clothes,197.4
2009,Jan,Men's Footwear,210
2009,Jan,Baby Clothes,240.9
2009,Jan,Men's Clothes,246
2009,Jan,Swimwear,262.4
2009,Jan,Women's Clothes,262.56
2009,Jan,Sportswear,265.86
2009,Jan,Women's Clothes,400.98
2009,Jan,Men's Clothes,430.5
2009,Jan,Sportswear,476.5
2009,Jan,Baby Clothes,487.35
2009,Jan,Children's Clothes,509.8
2009,Jan,Men's Clothes,541.8
2009,Jan,Women's Clothes,623.2
2009,Jan,Women's Clothes,640.2
2009,Jan,Sportswear,650.5
2009,Jan,Children's Clothes,851.55
2009,Jan,Children's Clothes,872.8
2009,Jan,Children's Clothes,1743.7
2009,Jan,Women's Clothes,1899.6
2009,Jan,-,-
2009,Feb,Baby Clothes,4.37
2009,Feb,Men's Clothes,21.65
2009,Feb,Sportswear,100.94
2009,Feb,Sportswear,101.4
2009,Feb,Swimwear,173.85
2009,Feb,Baby Clothes,188.4
2009,Feb,Sportswear,204.8
2009,Feb,Women's Clothes,219.7
2009,Feb,Men's Clothes,257.6
2009,Feb,Women's Footwear,291.4
2009,Feb,Women's Clothes,357.9
2009,Feb,Men's Clothes,368.5
2009,Feb,Women's Clothes,429
2009,Feb,Sportswear,454.8
2009,Feb,Men's Clothes,479.25
2009,Feb,Baby Clothes,681.25
2009,Feb,Women's Clothes,691.29
2009,Feb,Baby Clothes,840
2009,Feb,Children's Clothes,916.86
2009,Feb,Women's Clothes,1150.35
2009,Feb,Children's Clothes,1618.05
2009,Feb,Children's Clothes,2124
2009,Feb,Sportswear,2199.4
2009,Feb,Women's Footwear,2674.7
2009,Feb,Sportswear,3433.17
2009,Feb,-,-
2009,Mar,Baby Clothes,24.48
2009,Mar,Sportswear,125.4
2009,Mar,Men's Footwear,130.5
2009,Mar,Men's Clothes,156.24
2009,Mar,Sportswear,163.65
2009,Mar,Baby Clothes,199.12
2009,Mar,Baby Clothes,219.3
2009,Mar,Baby Clothes,262.4
2009,Mar,Women's Clothes,264.72
2009,Mar,Baby Clothes,276.9
2009,Mar,Women's Clothes,303.15
2009,Mar,Sportswear,414.6
2009,Mar,Baby Clothes,688.8
2009,Mar,Men's Clothes,887.4
2009,Mar,Women's Footwear,1250.7
2009,Mar,Women's Footwear,1653
2009,Mar,Women's Clothes,2194.56
2009,Mar,Women's Clothes,2511.6
2009,Mar,-,-
2009,Apr,Sportswear,38.46
2009,Apr,Baby Clothes,89.6
2009,Apr,Men's Clothes,147.7
2009,Apr,Sportswear,213.15
2009,Apr,Men's Clothes,249.39
2009,Apr,Men's Clothes,263.7
2009,Apr,Sportswear,272.7
2009,Apr,Children's Clothes,304.15
2009,Apr,Women's Clothes,309.84
2009,Apr,Women's Clothes,333
2009,Apr,Men's Clothes,423.15
2009,Apr,Women's Clothes,453.6
2009,Apr,Sportswear,782.88
2009,Apr,Sportswear,2053.1
2009,Apr,Men's Footwear,2228.1
2009,Apr,-,-
2009,May,Baby Clothes,2.97
2009,May,Sportswear,33.2
2009,May,Sportswear,34.56
2009,May,Men's Clothes,57.08
2009,May,Baby Clothes,152.8
2009,May,Men's Clothes,156.78
2009,May,Swimwear,163.5
2009,May,Men's Clothes,186.54
2009,May,Sportswear,211.32
2009,May,Men's Clothes,214.8
2009,May,Sportswear,341.2
2009,May,Women's Clothes,366.8
2009,May,Sportswear,468
2009,May,Baby Clothes,512.4
2009,May,Women's Footwear,512.94
2009,May,Men's Footwear,619
2009,May,Women's Clothes,1013.94
2009,May,Men's Footwear,1828.6
2009,May,Women's Footwear,2554.2
2009,May,Sportswear,4486.8
2009,May,Women's Clothes,7766.82
2009,May,-,-
2009,Jun,Baby Clothes,32.45
2009,Jun,Women's Footwear,101.9
2009,Jun,Men's Clothes,110.75
2009,Jun,Sportswear,114.9
2009,Jun,Baby Clothes,132
2009,Jun,Baby Clothes,194
2009,Jun,Baby Clothes,202
2009,Jun,Sportswear,291.2
2009,Jun,Men's Clothes,348.8
2009,Jun,Sportswear,360.4
2009,Jun,Sportswear,394.4
2009,Jun,Women's Clothes,433.6
2009,Jun,Baby Clothes,474.48
2009,Jun,Sportswear,526.4
2009,Jun,Women's Footwear,634.32
2009,Jun,Baby Clothes,764.12
2009,Jun,Men's Clothes,917
2009,Jun,Sportswear,1056.3
2009,Jun,Men's Clothes,1415
2009,Jun,Men's Clothes,1608
2009,Jun,Sportswear,2114.4
2009,Jun,Women's Clothes,3986.3
2009,Jun,Women's Footwear,5688.9
2009,Jun,Women's Clothes,7228.6
2009,Jun,-,-
2009,Jul,Sportswear,14.96
2009,Jul,Baby Clothes,39.2
2009,Jul,Sportswear,51.1
2009,Jul,Women's Clothes,65.6
2009,Jul,Women's Footwear,83.3
2009,Jul,Sportswear,104.5
2009,Jul,Baby Clothes,120.8
2009,Jul,Men's Clothes,141.9
2009,Jul,Women's Clothes,181.2
2009,Jul,Baby Clothes,184.8
2009,Jul,Sportswear,187.6
2009,Jul,Baby Clothes,204.4
2009,Jul,Sportswear,204.54
2009,Jul,Baby Clothes,263.1
2009,Jul,Sportswear,266.7
2009,Jul,Baby Clothes,270.4
2009,Jul,Swimwear,272.75
2009,Jul,Women's Clothes,298.48
2009,Jul,Baby Clothes,325.36
2009,Jul,Men's Clothes,325.85
2009,Jul,Sportswear,369.5
2009,Jul,Women's Footwear,374.8
2009,Jul,Baby Clothes,416.16
2009,Jul,Children's Clothes,439.84
2009,Jul,Baby Clothes,469.26
2009,Jul,Men's Clothes,504.24
2009,Jul,Women's Clothes,553.2
2009,Jul,Sportswear,554.2
2009,Jul,Women's Clothes,688.05
2009,Jul,Women's Clothes,688.9
2009,Jul,Men's Footwear,968
2009,Jul,Women's Clothes,1034.4
2009,Jul,Sportswear,1088.85
2009,Jul,Sportswear,1214.8
2009,Jul,Women's Footwear,1257.9
2009,Jul,Women's Footwear,1629.2
2009,Jul,Men's Clothes,1823.4
2009,Jul,Men's Footwear,2421.25
2009,Jul,Women's Footwear,2804.2
2009,Jul,Women's Clothes,4645.2
2009,Jul,Sportswear,4803.6
2009,Jul,Men's Footwear,5042.5
2009,Jul,Women's Clothes,5809.44
2009,Jul,Women's Clothes,17427.9
2009,Jul,Women's Clothes,19557.5
2009,Jul,-,-
2009,Aug,Sportswear,37.4
2009,Aug,Sportswear,41
2009,Aug,Men's Clothes,67.8
2009,Aug,Baby Clothes,101.28
2009,Aug,Women's Clothes,113.76
2009,Aug,Men's Clothes,172.2
2009,Aug,Men's Footwear,209.84
2009,Aug,Sportswear,275.4
2009,Aug,Men's Clothes,276.6
2009,Aug,Men's Clothes,298.55
2009,Aug,Women's Clothes,351.42
2009,Aug,Women's Footwear,363.6
2009,Aug,Sportswear,388.8
2009,Aug,Children's Clothes,463.65
2009,Aug,Sportswear,487.8
2009,Aug,Sportswear,529.8
2009,Aug,Sportswear,562.35
2009,Aug,Sportswear,577.4
2009,Aug,Children's Clothes,617.4
2009,Aug,Sportswear,633.6
2009,Aug,Children's Clothes,682.78
2009,Aug,Sportswear,756
2009,Aug,Men's Clothes,838.8
2009,Aug,Sportswear,855.96
2009,Aug,Swimwear,960
2009,Aug,Sportswear,1074.3
2009,Aug,Men's Clothes,1164.6
2009,Aug,Baby Clothes,1215.6
2009,Aug,Men's Clothes,1230
2009,Aug,Men's Footwear,1410.75
2009,Aug,Women's Footwear,4579.2
2009,Aug,Women's Clothes,11229.9
2009,Aug,Women's Clothes,12119.1
2009,Aug,-,-
2009,Sep,Men's Clothes,22.05
2009,Sep,Baby Clothes,64.25
2009,Sep,Men's Clothes,172.32
2009,Sep,Children's Clothes,348.32
2009,Sep,Women's Footwear,415.4
2009,Sep,Women's Clothes,872.55
2009,Sep,Children's Clothes,998.8
2009,Sep,Women's Footwear,1090.2
2009,Sep,Women's Footwear,1510.4
2009,Sep,Women's Footwear,2147.5
2009,Sep,-,-
2009,Oct,Women's Footwear,27.24
2009,Oct,Men's Clothes,65.36
2009,Oct,Men's Clothes,89.28
2009,Oct,Women's Clothes,110.7
2009,Oct,Sportswear,164.8
2009,Oct,Women's Clothes,168.14
2009,Oct,Sportswear,182.8
2009,Oct,Sportswear,193.5
2009,Oct,Swimwear,237.4
2009,Oct,Women's Clothes,269.64
2009,Oct,Sportswear,324
2009,Oct,Baby Clothes,400.95
2009,Oct,Women's Clothes,432.8
2009,Oct,Baby Clothes,497
2009,Oct,Sportswear,513
2009,Oct,Women's Footwear,542.55
2009,Oct,Sportswear,599.2
2009,Oct,Sportswear,794.4
2009,Oct,Men's Clothes,829.8
2009,Oct,Men's Footwear,1014.3
2009,Oct,Women's Footwear,1087.5
2009,Oct,Women's Clothes,1270.8
2009,Oct,Women's Clothes,13366.85
2009,Oct,Women's Clothes,32075.2
2009,Oct,-,-
2009,Nov,Women's Footwear,13.68
2009,Nov,Women's Footwear,21.96
2009,Nov,Baby Clothes,37.86
2009,Nov,Baby Clothes,39.4
2009,Nov,Sportswear,43
2009,Nov,Sportswear,48.7
2009,Nov,Sportswear,54.74
2009,Nov,Men's Clothes,59.52
2009,Nov,Sportswear,68.85
2009,Nov,Sportswear,69.6
2009,Nov,Men's Clothes,76.3
2009,Nov,Sportswear,79.1
2009,Nov,Men's Clothes,87.4
2009,Nov,Children's Clothes,108.85
2009,Nov,Swimwear,111.84
2009,Nov,Swimwear,111.86
2009,Nov,Baby Clothes,127.44
2009,Nov,Men's Clothes,136.57
2009,Nov,Baby Clothes,148.2
2009,Nov,Sportswear,159.84
2009,Nov,Men's Clothes,163.4
2009,Nov,Men's Clothes,164.8
2009,Nov,Sportswear,181.4
2009,Nov,Children's Clothes,196.68
2009,Nov,Men's Clothes,197.6
2009,Nov,Men's Clothes,212
2009,Nov,Sportswear,226.25
2009,Nov,Swimwear,232.5
2009,Nov,Baby Clothes,239.6
2009,Nov,Sportswear,255.7
2009,Nov,Sportswear,267.5
2009,Nov,Women's Clothes,278
2009,Nov,Swimwear,280
2009,Nov,Sportswear,284.7
2009,Nov,Women's Clothes,303
2009,Nov,Baby Clothes,314
2009,Nov,Baby Clothes,314.08
2009,Nov,Swimwear,314.64
2009,Nov,Sportswear,315.3
2009,Nov,Swimwear,350.64
2009,Nov,Men's Footwear,352.8
2009,Nov,Swimwear,363.96
2009,Nov,Men's Clothes,381.12
2009,Nov,Women's Clothes,385
2009,Nov,Women's Clothes,393.75
2009,Nov,Women's Clothes,394
2009,Nov,Women's Clothes,422.1
2009,Nov,Men's Clothes,423
2009,Nov,Men's Clothes,423.6
2009,Nov,Sportswear,423.6
2009,Nov,Baby Clothes,466.8
2009,Nov,Women's Clothes,512.4
2009,Nov,Baby Clothes,536.34
2009,Nov,Women's Footwear,579.46
2009,Nov,Sportswear,593.7
2009,Nov,Sportswear,603.2
2009,Nov,Children's Clothes,624.64
2009,Nov,Men's Clothes,643.3
2009,Nov,Swimwear,660.4
2009,Nov,Men's Footwear,730.52
2009,Nov,Sportswear,785.75
2009,Nov,Swimwear,795.2
2009,Nov,Sportswear,838.8
2009,Nov,Women's Footwear,848.4
2009,Nov,Men's Clothes,1026
2009,Nov,Men's Clothes,1046.4
2009,Nov,Sportswear,1116.85
2009,Nov,Women's Clothes,1264.6
2009,Nov,Baby Clothes,1319.1
2009,Nov,Children's Clothes,1988.28
2009,Nov,Sportswear,2320
2009,Nov,Children's Clothes,2596.8
2009,Nov,Men's Footwear,5005.92
2009,Nov,-,-
2009,Dec,Sportswear,6.57
2009,Dec,Men's Clothes,15.76
2009,Dec,Men's Clothes,26.16
2009,Dec,Sportswear,29.01
2009,Dec,Men's Clothes,51.52
2009,Dec,Women's Footwear,52.65
2009,Dec,Men's Clothes,58.41
2009,Dec,Baby Clothes,62.4
2009,Dec,Sportswear,63.9
2009,Dec,Men's Clothes,65.92
2009,Dec,Sportswear,68.24
2009,Dec,Baby Clothes,72.54
2009,Dec,Men's Clothes,72.75
2009,Dec,Swimwear,74.34
2009,Dec,Men's Clothes,76.3
2009,Dec,Swimwear,78.72
2009,Dec,Women's Footwear,81.18
2009,Dec,Women's Clothes,90.6
2009,Dec,Sportswear,99
2009,Dec,Men's Clothes,101.55
2009,Dec,Swimwear,104.88
2009,Dec,Baby Clothes,109.56
2009,Dec,Sportswear,111.24
2009,Dec,Swimwear,117.32
2009,Dec,Sportswear,131.25
2009,Dec,Swimwear,138.18
2009,Dec,Men's Clothes,144.16
2009,Dec,Sportswear,179.2
2009,Dec,Men's Clothes,183
2009,Dec,Sportswear,184.17
2009,Dec,Women's Clothes,184.5
2009,Dec,Sportswear,192
2009,Dec,Men's Clothes,238.24
2009,Dec,Swimwear,281.76
2009,Dec,Sportswear,290.7
2009,Dec,Children's Clothes,295.8
2009,Dec,Sportswear,299.1
2009,Dec,Men's Clothes,309.12
2009,Dec,Baby Clothes,316.75
2009,Dec,Sportswear,332.4
2009,Dec,Baby Clothes,364.5
2009,Dec,Baby Clothes,374.22
2009,Dec,Sportswear,392.4
2009,Dec,Men's Clothes,392.5
2009,Dec,Swimwear,393.4
2009,Dec,Sportswear,436.8
2009,Dec,Men's Clothes,455.75
2009,Dec,Swimwear,477.2
2009,Dec,Baby Clothes,486
2009,Dec,Sportswear,527.85
2009,Dec,Sportswear,541.2
2009,Dec,Sportswear,573.44
2009,Dec,Sportswear,604.6
2009,Dec,Men's Clothes,609
2009,Dec,Men's Footwear,611.25
2009,Dec,Men's Clothes,634.9
2009,Dec,Children's Clothes,653.7
2009,Dec,Women's Clothes,661.8
2009,Dec,Children's Clothes,789.66
2009,Dec,Men's Footwear,856.02
2009,Dec,Men's Clothes,873.9
2009,Dec,Women's Footwear,899.36
2009,Dec,Women's Clothes,923
2009,Dec,Sportswear,1259.1
2009,Dec,Baby Clothes,1337.7
2009,Dec,Women's Clothes,1647.2
2009,Dec,Men's Clothes,1723.2
2009,Dec,Men's Footwear,2818.8
2009,Dec,-,-

](delimiter is ',') ;

temp:
load *,
rowno() as RowID;
load
CategoryName,
year(date#([Year],'yyyy')) as Year,
date#([Month],'mmm') as [Month],
[Gross Sales]
resident [Sample] order by CategoryName,Year,Month;

final: load*,
if(RowID=1,[Gross Sales],if(peek('CategoryName')<>CategoryName,[Gross Sales],[Gross Sales]+peek('cumul'))) as cumul

resident temp
order by CategoryName,Year,Month;
drop table [Sample];
drop table temp;

resulting table

Re: Calculate annual accumulated sales amount by year, month and category in the Data load editor

SMalli — Tue, 19 Jul 2022 13:27:04 GMT

unfortunately its not working. This is the output

Re: Calculate annual accumulated sales amount by year, month and category in the Data load editor

brunobertels — Tue, 19 Jul 2022 15:17:15 GMT

you need to add RowID dimension in your table

Re: Annual accumulated sales amount by year, month and category in the Data load editor

SMalli — Tue, 19 Jul 2022 18:07:08 GMT

Quick question, for some of the months the values are null

example :2008 July 500

2008 July -

So for nulls , i want to replace it with the above value

Re: Annual accumulated sales amount by year, month and category in the Data load editor

brunobertels — Thu, 21 Jul 2022 12:02:25 GMT

in that case in the script you can peek above row to populate null value

may be this

if(isnull([Gross Sales]) and CategoryName = peek(CategoryName) ,peek([Gross Sales]), [Gross Sales]) as [Gross Sales]

Be carefull your accumulation will be impacted

Regards

topic Re: Calculate annual accumulated sales amount by year, month and category in the Data load editor in App Development

Annual accumulated sales amount by year, month and category in the Data load editor

Re: Calculate annual accumulated sales amount by year, month and category in the Data load editor

Re: Calculate annual accumulated sales amount by year, month and category in the Data load editor

Re: Calculate annual accumulated sales amount by year, month and category in the Data load editor

Re: Annual accumulated sales amount by year, month and category in the Data load editor

Re: Annual accumulated sales amount by year, month and category in the Data load editor

Re: Annual accumulated sales amount by year, month and category in the Data load editor

Re: Annual accumulated sales amount by year, month and category in the Data load editor