I am being a Merchant, I need to know if the Sales is happening or not and for which Product sales is happening.
My Sales will happen 24*7 and my sales will be calculated every 30 minutes.
Below are my Product Details:
Product Name |
Shirt |
Dhoti |
Sarees |
Towel |
T-Shirt |
Pant |
Churidar |
Inner wears |
Apparels |
Others |
One day Sample data for one Product is as below:
Product Name | Date | Hour | Minute | Sale Status |
Shirt | 1/1/2019 | 0 | 0 | 1 |
Shirt | 1/1/2019 | 0 | 30 | 1 |
Shirt | 1/1/2019 | 1 | 0 | 1 |
Shirt | 1/1/2019 | 1 | 30 | 1 |
Shirt | 1/1/2019 | 2 | 0 | 1 |
Shirt | 1/1/2019 | 2 | 30 | 1 |
Shirt | 1/1/2019 | 3 | 0 | 1 |
Shirt | 1/1/2019 | 3 | 30 | 1 |
Shirt | 1/1/2019 | 4 | 0 | 1 |
Shirt | 1/1/2019 | 4 | 30 | 1 |
Shirt | 1/1/2019 | 5 | 0 | 1 |
Shirt | 1/1/2019 | 5 | 30 | 1 |
Shirt | 1/1/2019 | 6 | 0 | 1 |
Shirt | 1/1/2019 | 6 | 30 | 1 |
Shirt | 1/1/2019 | 7 | 0 | 1 |
Shirt | 1/1/2019 | 7 | 30 | 1 |
Shirt | 1/1/2019 | 8 | 0 | 1 |
Shirt | 1/1/2019 | 8 | 30 | 1 |
Shirt | 1/1/2019 | 9 | 0 | 1 |
Shirt | 1/1/2019 | 9 | 30 | 1 |
Shirt | 1/1/2019 | 10 | 0 | 1 |
Shirt | 1/1/2019 | 11 | 0 | 1 |
Shirt | 1/1/2019 | 11 | 30 | 1 |
Shirt | 1/1/2019 | 12 | 0 | 1 |
Shirt | 1/1/2019 | 12 | 30 | 1 |
Shirt | 1/1/2019 | 13 | 30 | 1 |
Shirt | 1/1/2019 | 14 | 0 | 1 |
Shirt | 1/1/2019 | 15 | 0 | 1 |
Shirt | 1/1/2019 | 15 | 30 | 1 |
Shirt | 1/1/2019 | 16 | 0 | 1 |
Shirt | 1/1/2019 | 17 | 0 | 1 |
Shirt | 1/1/2019 | 17 | 30 | 1 |
Shirt | 1/1/2019 | 18 | 0 | 1 |
Shirt | 1/1/2019 | 18 | 30 | 1 |
Shirt | 1/1/2019 | 19 | 0 | 1 |
Shirt | 1/1/2019 | 20 | 0 | 1 |
Shirt | 1/1/2019 | 21 | 0 | 1 |
Shirt | 1/1/2019 | 21 | 30 | 1 |
Shirt | 1/1/2019 | 23 | 0 | 1 |
Shirt | 1/1/2019 | 23 | 30 | 1 |
Now I need to calculate the sum of Sales status per day for all Products.
Tried Method:
Created a Reference sheet which have Product name, Date, Hour & Minute. For example, as below and the table will keep growing for all days of a month and for all Products. And I used a Lookupvalue and If Condition function with the Source data and achieved the output in a seprertae column and sum that.
Product Name | Date | Hour | Minute |
Shirt | 1/1/2019 | 0 | 0 |
Shirt | 1/1/2019 | 0 | 30 |
Shirt | 1/1/2019 | 1 | 0 |
Shirt | 1/1/2019 | 1 | 30 |
Shirt | 1/1/2019 | 2 | 0 |
Shirt | 1/1/2019 | 2 | 30 |
Shirt | 1/1/2019 | 3 | 0 |
Shirt | 1/1/2019 | 3 | 30 |
Shirt | 1/1/2019 | 4 | 0 |
Shirt | 1/1/2019 | 4 | 30 |
Shirt | 1/1/2019 | 5 | 0 |
Shirt | 1/1/2019 | 5 | 30 |
Shirt | 1/1/2019 | 6 | 0 |
Shirt | 1/1/2019 | 6 | 30 |
Shirt | 1/1/2019 | 7 | 0 |
Shirt | 1/1/2019 | 7 | 30 |
Shirt | 1/1/2019 | 8 | 0 |
Shirt | 1/1/2019 | 8 | 30 |
Shirt | 1/1/2019 | 9 | 0 |
Shirt | 1/1/2019 | 9 | 30 |
Shirt | 1/1/2019 | 10 | 0 |
.
.
.
.
.
.
Now the problem is:
For one product, 1product*30days*24Hours*2TimeInterval = 1440 rows, so for 10 Products it is 14400 rows and for 12 months 1,72,800 rows and if the product increases and for two/three years, the data file size becomes very big. If the product is more than 100, can’t even imagine the size of the file.
Is there any way to overcome this ????
@v-juanli-msft @Greg_Deckler @ImkeF @Ashish_Mathur
