Creating a Subtotal Table- Invalid Expression

jason_nicholas — Mon, 19 Jun 2017 18:56:25 GMT

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.

Re: Creating a Subtotal Table- Invalid Expression

aarkay29 — Mon, 19 Jun 2017 19:03:13 GMT

Use group by clause

Subtotal:

LOAD DISTINCT ([Booking ID]),

sum(Cost) as Subtotal

Resident SourceTable

Group By [Booking ID];

Re: Creating a Subtotal Table- Invalid Expression

jason_nicholas — Tue, 20 Jun 2017 11:50:08 GMT

This worked. I didn't really understand the function of Group By in this context, but I just searched some other threads and am starting to get the picture. Thanks!

topic Re: Creating a Subtotal Table- Invalid Expression in QlikView

Creating a Subtotal Table- Invalid Expression

Re: Creating a Subtotal Table- Invalid Expression

Re: Creating a Subtotal Table- Invalid Expression