Forum Discussion
Sum by multiple filters for hour range
Please Help
I have a large dataset with sales information aggregated by date, store#, and most imporantly by hour. See example below;
| Date | StoreId | Hour | Online Orders | Phone Orders | Total Orders |
| 1-Jan | 1 | 11 | 50 | 147 | 197 |
| 1-Jan | 2 | 10 | 50 | 200 | 250 |
| 1-Jan | 1 | 13 | 0 | 210 | 210 |
| 1-Jan | 1 | 12 | 15 | 175 | 190 |
| 1-Jan | 2 | 13 | 0 | 275 | 275 |
| 1-Jan | 2 | 11 | 0 | 108 | 108 |
| 1-Jan | 1 | 10 | 0 | 75 | 75 |
| 1-Jan | 2 | 14 | 0 | 89 | 89 |
This dataset is not ordered - essentially - I'm trying to create a column flag of T/F to identify when a store has 3 consecutive hours of NO digital sales but Total Orders were over X.
=AND(SUMIFS(OnlineOrders,DATE,[@DATE],StoreId,[@StoreId],Hour,OR([@Hour],[@Hour]+1,[@Hour]-1)) = 0, SUMIFS(TotalOrders,DATE,[@DATE],StoreId,[@StoreId],Hour,OR([@Hour],[@Hour]+1,[@Hour]-1)) > X
Can someone please help?
Here's a pattern for a calculated column. You can adjust the threshold variable as well as the logic for handling cases where three consecutive hours don't exist.
Flag = VAR vThreshold = 175 VAR vHour = Table1[Hour] VAR vTotalOrders = Table1[Total Orders] VAR vOnlineOrdersLast3Hours = CALCULATE ( SUM ( Table1[Online Orders] ), ALLEXCEPT ( Table1, Table1[Date], Table1[StoreId] ), Table1[Hour] >= vHour - 2, Table1[Hour] <= vHour ) VAR vResult = IF ( vOnlineOrdersLast3Hours == 0 && vTotalOrders > vThreshold, "T", "F" ) RETURN vResult
2 Replies
- DataInsights
Super User
Here's a pattern for a calculated column. You can adjust the threshold variable as well as the logic for handling cases where three consecutive hours don't exist.
Flag = VAR vThreshold = 175 VAR vHour = Table1[Hour] VAR vTotalOrders = Table1[Total Orders] VAR vOnlineOrdersLast3Hours = CALCULATE ( SUM ( Table1[Online Orders] ), ALLEXCEPT ( Table1, Table1[Date], Table1[StoreId] ), Table1[Hour] >= vHour - 2, Table1[Hour] <= vHour ) VAR vResult = IF ( vOnlineOrdersLast3Hours == 0 && vTotalOrders > vThreshold, "T", "F" ) RETURN vResultI cannot appreciate this enough!
