Hi I have the following data
| December | January | February | March | April |
|---|---|---|---|---|
| 0 | 0 | 0 | 0 | 116282 |
| 0 | 0 | 116282 | 116282 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
| 122207 | 0 | 0 | 0 | 0 |
| 0 | 122207 | 122207 | 0 | 0 |
This data is currently being held in a dynamically generated range and has 2414 rows and 27 columns. Each value represents the cross section of case numbers that receive retro payments in a given month – for instance, E means december, and case number 122207 receives a retro payment in december, column G is February, and case number 116282 and 122207 both receive retro payment in February.
I generate the range through the following formula:
=LET(a, RetroTable[Case Number],b, RetroTable[Retro Units], c, CHOOSECOLS(RetroTable, XMATCH(Analysis!A2#, RetroTable[#Headers])), a*ABS(b*c))
With A2# being a 27×1 array of months (2 years and 3 months of retro units tracking.
Basically what happens is if there’s a non-0 number in the retro units column of retro table and a non-zero number in the particular month column in the table, then it’s considered an aging case, and I want to document it as such. Then multiplying it by the actual number and I get the actual case number.
But what I want is an array that has only the non-zero numbers. I have tried adding in a filter condition, such as:
=LET(a, RetroTable[Case Number],b, RetroTable[Retro Units], c, CHOOSECOLS(RetroTable, XMATCH(Analysis!A2#, RetroTable[#Headers])), filter(a*ABS(b*c), a*ABS(b*c)<>0))
But this produces a #VALUE error
I have tried the small formula
=LET(a, RetroTable[Case Number],b, RetroTable[Retro Units], c, CHOOSECOLS(June24_Details, XMATCH(Analysis!A2#, RetroTable[#Headers])), SMALL(IF(a*ABS(b*c)=0, "", a*ABS(b*c)), SEQUENCE(ROWS(RetroTable))))
But as the data is needed on a monthly basis, this formula doesn’t work because it loses the column/row integrity. It turns everything into a single column, and I need to keep each month separated but connected.