I am trying to follow the suggestion in this post, but I am getting an error "Invalid Expression". Apologies, but I can't upload content.
Source data looks like:
Booking ID, Item, Item Amount
111, Item1, 100
111, Item2, 200
111, Discount, 30
123, Item1, 100
and so on. Each entry (ID) might have a number of items, each with an associated cost. The discount amount also appears in the same data set as a positive number. First, to break the discount out from the rest of the items, I use the following in the primary fact table load script:
SourceTable:
LOAD [Booking ID],
if(match(Item, 'Discount'), [Item Amount]) as DiscountTemp,
if(not match(Item, 'Discount'), [Item Amount]) as Cost
FROM source
Discount:
LOAD DISTINCT ([Booking ID]),
DiscountTemp as [Discount]
Resident SourceTable;
Drop Field DiscountTemp
this gives me a table with the single discount entry associated with the Booking ID. Because any given ID would have either no discount, or only one single entry, this works perfectly.
However, I want to create a variable to determine what percentage discount is applied per Booking ID. I want to subtotal all of the [Cost] values per [Booking ID] so that I can determine the discount percentage.
Per the linked thread, I created a resident table:
Subtotal:
LOAD DISTINCT ([Booking ID]),
sum(Cost) as Subtotal
Resident SourceTable;
I expect this to create a table just like the Discount table, but instead of the discount, the value should be the total of all other items in a Booking ID. I want to divide the Discount/Subtotal to create a percentage, which I want to be a variable to use throughout my charts. For my example above, I would want a subtotal of 300 for [Booking ID] 111, so that I can factor in the 30 discount, to create a variable of 10% as the discount total. Then, if I just look at 'Item 1' in a chart, the total cost should return as 90.
Can anyone explain my "Invalid Expression"? Are there better ways to do this?
The reason for the whole effort is that, since the discount comes in as a lump sum and not as a percentage, I need to be able to accurately apply the discount to the cost per item when drilling down my charts. Right now, I can sum all costs, and subtract the discount so I get an accurate total for the whole [Booking ID], but when I select a single item, it would try to subtract the whole discount rather than just the portion applied to the item.
