# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for
Did you mean:
Highlighted
Contributor III

## Total Cost by Order

Hi there

I have a schema that looks like this where 1 Order Item has Many Menu Items

1

1

1

2

2

2

3

3

3

1                                   10.00

2                                   20.00

3                                   30.00

When I use =sum([Cost]) I get \$60 which seems to come from Table 2

I need to have the Cost pulled across to the Order Items table so I get \$180 = (10x3) + (20*3) + (30*3) from table 1.

This seems so simple. I must be missing something really basic.

Neil

1 Solution

Accepted Solutions
Highlighted
Partner

Hi Neil,

I should advice a script solution on this on.

Cost

If you can't edit the script then you could calculate the correct number with this expression (assuming that there is only 1 cost value per Order-Menu-Item). :

8 Replies
Highlighted
Partner

Hi Neil,

I should advice a script solution on this on.

Cost

If you can't edit the script then you could calculate the correct number with this expression (assuming that there is only 1 cost value per Order-Menu-Item). :

Highlighted
Partner

try

Highlighted
Creator

Hi,

I recreated it in Inline loads and provided the following solution:

I basically used the script to create a new field called count then manipulated it on the front end with:

Sum(CountOfDistinctItems*Cost)

Highlighted
Contributor III

thanks for you reply, but that just gives me a total thats way, way too large

Highlighted
Contributor III

thanks Aron but what i didn't include was the fact that there is another table attached to the "Order Items" called Orders which has a date on it which would break your solution because the CountOfDistinctItems will vary over time for each Item.

I appreciate your suggestion, so thank you.

Neil

Highlighted
Contributor III

thanks A.M.

The LEFT JOIN worked. Just have a few questions to better understand the solution if you don't mind.

So I started with this...

and after adding the LEFT JOIN I ended up with this...

I do have a basic understanding of SQL joins and relation schemas, but can I ask in Qlik, are the Order Item and Menu Item tables physically combined and is the data from the Menu Item table duplicated or is this just a virtual representation of the data structure.

Also, the relationship is one-to-many from left to right (as shown in the first image) and I was able to LEFT JOIN the first 2 tables as well and end up with this

I tried to LEFT JOIN these 2 tables but got this error

My question is do I need to LEFT JOIN all 4 tables given there is a one-to-many relationship across them all and one of the KPIs I'd like to calculated is Average Order Cost By Customer

thanks so much for you help.

Neil

Highlighted
Partner

Hi Neil,

So first of all answering your last question: you don't need to join all tables. When you join tables like the order menu item in your initial question it multiplies to rows from the [Menu Item] table times the rows of the [Order Item] table.

Qlikview works basically like SQL. There are many examples of using joins found when using Google.

SQL JOINS

Highlighted
Contributor

I do have a basic understanding of SQL joins and relation schemas, but can I ask in Qlik, are the Order Item and Menu Item tables physically combined and is the data from the Menu Item table duplicated or is this just a virtual representation of the data structure