Forum Discussion
Need help on table or formula
Hello,
I'm having issue regarding formula, I'm new to this languange and currently studying it.
here's what I got stucked.
So I have this Table
| Item# | Time Stamp | Hour | Quantity |
| A | 1:43:01 PM | 13 | 1 |
| B | 1:48:01 PM | 13 | 1 |
| C | 1:47:01 PM | 13 | 1 |
| A | 2:39:03 PM | 14 | 1 |
| B | 2:43:12 PM | 14 | 1 |
| C | 3:59:13 PM | 15 | 1 |
| C | 4:03:53 PM | 16 | 1 |
| C | 4:12:29 PM | 16 | 1 |
| C | 4:12:29 PM | 16 | 1 |
But I need to summarize the report by hourly to show on the graph.
The challenging part for me is I need to rank by hour the item # thats the earliest (or lowest timestamp), and sum the total results under all hours. If it doesnt make sense. basically these is what im looking for.
Rank lowest time stamp - shows the item# with the lowest time stamp for that hour.
| Hour | Rank Lowest Time Stamp | Results |
| 13 | A | 3 |
| 14 | A | 2 |
| 15 | C | 1 |
| 16 | C | 3 |
Anonymous Please try this as a "New Table"
Test11Out = VAR _Time = ADDCOLUMNS(SUMMARIZE(Test11,Test11[Hour],"MinTime",MIN(Test11[Time Stamp])),"Item",LOOKUPVALUE(Test11[Item#],Test11[Hour],[Hour],Test11[Time Stamp],[MinTime])) VAR _Totals = SUMMARIZE(Test11,Test11[Hour],"Results",SUM(Test11[Quantity])) RETURN NATURALINNERJOIN(_Time,_Totals)
Added the time as well, just for reference.
2 Replies
Maybe look to add an index/ranking column within each hour in your dataset and use the lowest value of said index as a filter? Ranking within a group's a fairly common problem so you should be able to search and find something that works within your setup?
Anonymous Please try this as a "New Table"
Test11Out = VAR _Time = ADDCOLUMNS(SUMMARIZE(Test11,Test11[Hour],"MinTime",MIN(Test11[Time Stamp])),"Item",LOOKUPVALUE(Test11[Item#],Test11[Hour],[Hour],Test11[Time Stamp],[MinTime])) VAR _Totals = SUMMARIZE(Test11,Test11[Hour],"Results",SUM(Test11[Quantity])) RETURN NATURALINNERJOIN(_Time,_Totals)
Added the time as well, just for reference.
