Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Struggling with straight table totals

Hello,

I need to calculate Totals on a straight table.

Sample straight table -

ProductTotal salesTotal cost
Margin

1000800
-
Product1500400
20%
Product2300250
17%
Product3200150
25%

As a source I have 2 tables -

1. Sales (with Product IDs, quantities and sale prices)

2. Inventory (with unique Product IDs and costs)

Product ID is a dimension.

Total Sales is simply a sum of Sale prices from Sales table

Total Cost is sum (Qty (from sales table)) *Cost (from Inventory table)

Margin is (Sale - Cost) / Sale

Now total margin column is for some reason blank. It shouldn't be a sum of all margins but (Total sales - total cost) / total sales

How can I do that?

I read something about aggr function but not sure I understand how it works.

Many thanks!

1 Solution

Accepted Solutions

Re: Struggling with straight table totals

Hi

This may be because of expression problems.

Please check the attached the file and check this is what you have written as expressions.

3 Replies
vgutkovsky
Honored Contributor II

Re: Struggling with straight table totals

Your logic is correct, so the only reason it may not be working is some sort of syntax error. Make sure your expression look like this:

Sales

sum(Sales) //substitute your actual field name

Cost

sum(Qty)*sum(Cost) //substitute your actual field names

Margin

("Sales" - "Cost")/("Sales") //note that this uses the expression titles set above--they should match exactly

Regards,

Vlad

Re: Struggling with straight table totals

Hi

This may be because of expression problems.

Please check the attached the file and check this is what you have written as expressions.

Not applicable

Re: Struggling with straight table totals

great it worked. it looks like I had an error in my formula. Thansk!

Community Browser