I am trying to create groupings in a Webi table that are based off of a Sum measure.
I have a list of invoices with associated payments. Some invoices have multiple payments and can span over multiple years. I want to sum the total payments per year for an invoice and then group those results into 'Total Ranges'
and so on.
My final table I want to just see a count of invoices for each year that fall into each of these ranges
Total payments for invoice 20040006 for 2004 = $1348 so that falls into the 1000-2000 range
Total payments for invoice 20040007 for 2004 = $172 so that falls into the 0-1000 range
My final table would look like this
The Payment field is a Sum measure from the universe.
I have created a dimension variable to assign the group label based on the total payments
I created a vertical table with a group on the PaymentRanges dimension variable
If i keep the report filtered to 1 year, it displays the correct values for that year. I dont want to create another break on the Year field because it's too messy
Id like for it to be a cross tab report but when i try to create that, the PaymentRange break down is lost as everything is rolled up for the entire year.
Any thoughts on how to accomplish this? I think I need to assign some context to the measure variable used in the cross tab but I'm not sure how.
If I need to offer any additional information, please let me know.