Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

neil_gabin
New Contributor III

Total Cost by Order

Hi there

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

2017-11-27 11.29.28 pm.png

Order-Menu-Items

1

1

1

2

2

2

3

3

3

Order Menu Items     Cost

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
avkeep01
Valued Contributor

Re: Total Cost by Order

Hi Neil,

I should advice a script solution on this on.

In the Script add a left join to the Order-Menu-Items.

LEFT JOIN ('Order Items') LOAD

Order-Menu-Items,

Cost

FROM Menu;

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). :

COUNT(Order-Menu-Items) * Cost.

7 Replies
avkeep01
Valued Contributor

Re: Total Cost by Order

Hi Neil,

I should advice a script solution on this on.

In the Script add a left join to the Order-Menu-Items.

LEFT JOIN ('Order Items') LOAD

Order-Menu-Items,

Cost

FROM Menu;

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). :

COUNT(Order-Menu-Items) * Cost.

shraddha_g
Honored Contributor III

Re: Total Cost by Order

try

Sum(Cost) * Count([Order-Menu-Items])

aronwilliamson
Contributor

Re: Total Cost by Order

Hi,

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

2017_11_27_16_00_39_baudercpsvr_Remote_Desktop_Connection.png

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

Sum(CountOfDistinctItems*Cost)


neil_gabin
New Contributor III

Re: Total Cost by Order

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

neil_gabin
New Contributor III

Re: Total Cost by Order

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

neil_gabin
New Contributor III

Re: Total Cost by Order

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...

2017-11-28 07.25.51 am.png

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

2017-11-28 07.24.50 am.png

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

2017-11-28 07.42.57 am.png

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

2017-11-28 07.47.41 am.png

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

avkeep01
Valued Contributor

Re: Total Cost by Order

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