Skip to Content
0
Sep 28, 2020 at 07:17 PM

Webi Table Creating Group from Measure

42 Views

Hello,

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'

Total Ranges:

0-1000

1000-2000

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

Example:

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.

Attachments

boog8.png (2.0 kB)
607dk.png (15.1 kB)
zyrj6.png (5.0 kB)
sn5iw.png (10.1 kB)
g0pa9.png (31.1 kB)