Forum Discussion
Netting WIP (with aging)
Hi all!
I have to make a report for the WIP with aging and do a netting with prebilled amounts.
For example:
1/ WIP of 690 and a prebilled amount of 700 => netting => 10 prebilled amount
2/ WIP of 450 and a prebilled amount of 400 => netting => 50 WIP
So far so good.
But like a said I have to make an aging table.. 😕
So in case 2:
WIP before netting
00-30 days: 50
31-60 days: 20
61-90 days: 80
... (rest): 300
Prebilled: 400
WIP after netting
00-30 days: 50
31-60 days: 0
61-90 days: 0
... (rest): 0
Prebilled: 0
Excel file with a data sample.
https://1drv.ms/x/s!Akj7bUrQd6tMxFRr7IyAuC9AHOXj?e=1mcHia
Can someone help me out?
Thanks a lot and have a wonderful weekend!
M
Hi marcvd,
I think you can create a calculated column with math functions to split/group the records based on particular intervals.
Group = VAR interval = 30 VAR offset = INT ( ( [Age] - 1 ) / interval ) RETURN offset * interval + IF ( offset > 0, 1, 0 ) & " ~ " & ( offset + 1 ) * intervalAfter these steps, you can create a visual based on the 'group' field as 'category' and unique 'id' field with aggregation mode 'count' to achieve your requirement.
Regards,
Xiaoxin Sheng
1 Reply
Hi marcvd,
I think you can create a calculated column with math functions to split/group the records based on particular intervals.
Group = VAR interval = 30 VAR offset = INT ( ( [Age] - 1 ) / interval ) RETURN offset * interval + IF ( offset > 0, 1, 0 ) & " ~ " & ( offset + 1 ) * intervalAfter these steps, you can create a visual based on the 'group' field as 'category' and unique 'id' field with aggregation mode 'count' to achieve your requirement.
Regards,
Xiaoxin Sheng