9 Replies Latest reply: Jul 4, 2018 7:02 PM by Sunny Talwar

# Sum function in Qlik Sence data editor does not work

Dear Qlik-Community,

I am new in Qlik Sence and have a major problem with the sum-function in the Qlik Sence data editor.

I have the following (sample) data set…

 ORDER_ID UNIT_PRICE QUANTITY 10248 34,8 5 10249 18,6 9 10250 7,7 10 10250 16,8 15 10251 16,8 20 10252 64,8 40 10253 10 20 10254 3,6 15 10255 15,2 20 10256 26,2 15 10257 35,1 25 10258 25,6 6 10259 8 10 10260 12 21 10261 14,4 20 10262 30,4 2 10263 8 36 10264 7,7 25 10265 31,2 30 10266 30,4 12 10267 14,7 50 10268 27,8 4 10269 2 60

With the columns “unit_price” and “quantity” I am able to create a new column called “revenue” (unit price * quantity). The result Looks like this:

(This is just a small extract!)

Know I would like to calculate the share of revenue (revenue / sum[revenue]) for every "order id".

This is my (not working) Code…

ORDER_ID,

UNIT_PRICE,

QUANTITY,

UNIT_PRICE*QUANTITY AS REVENUE,

(UNIT_PRICE*QUANTITY)/Sum(UNIT_PRICE*QUANTITY) AS SHARE

FROM [lib://Daten/order_details_3.csv]

(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

I am not sure if this sort of calculation is even possible in Qlik Sense, but if you have an idea or a code for my problem, please let me know.

Thanks for your time!

• ###### Re: Sum function in Qlik Sence data editor does not work

This is because aggregate functions usually need Group By Clause. Which dimensions are you want to do the sum over? Sum over Order_ID?

• ###### Re: Sum function in Qlik Sence data editor does not work

May be try this

TempTable:

UNIT_PRICE,

QUANTITY,

UNIT_PRICE*QUANTITY AS REVENUE,

(UNIT_PRICE*QUANTITY)/Sum(UNIT_PRICE*QUANTITY) AS SHARE

FROM [lib://Daten/order_details_3.csv]

(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

Left Join (TempTable)

Sum(REVENUE) as TOTALREVENUE

Resident TempTable

Group By ORDER_ID;

FinalTable:

UNIT_PRICE,

QUANTITY,

REVENUE,

REVENUE/TOTALREVENUE as SHARE

Resident TempTable;

DROP Table TempTable;

• ###### Re: Sum function in Qlik Sence data editor does not work

Thank you very much for trying to answer my question!

I tried your code (in many variations) but still get an error message (Invalid expression)... (-_-)

I am pretty sure that the cause of the error is “sum()”.

So what can I do? Is there perhaps a workaround to avoid the sum-function?

Thank you in advance.

• ###### Re: Sum function in Qlik Sence data editor does not work

Can you share a screenshot of the error message?

• ###### Re: Sum function in Qlik Sence data editor does not work

remove

(UNIT_PRICE*QUANTITY)/Sum(UNIT_PRICE*QUANTITY) AS SHARE

from your first tempTable

• ###### Re: Sum function in Qlik Sence data editor does not work

First of all many thanks to both of you (Sunny and Mikael) for your attempts to solve my Problem.

With Sunny´s code und Mikael´s adjustment I get a promising result.

Here is a screenshot!

My desired goal / outcome is a crosstab that looks like this (example with a shorter data set):

So basically I need the "revenue share" for each ORDER_ID, that turns to the overall revenue for all orders (ORDER_ID´s).

Sorry for the initial lack of clarity.

• ###### Re: Sum function in Qlik Sence data editor does not work

I saw the problem in my initial script... try this

TempTable:

Sum(UNIT_PRICE) as UNIT_PRICE,

Sum(QUANTITY) as QUANTITY,

Sum(UNIT_PRICE*QUANTITY) AS REVENUE

FROM [lib://Daten/order_details_3.csv]

(txt, codepage is 28591, embedded labels, delimiter is ';', msq)

Group By ORDER_ID;

Left Join (TempTable)

LOAD Sum(REVENUE) as TOTALREVENUE

Resident TempTable;

FinalTable:

UNIT_PRICE,

QUANTITY,

REVENUE,

REVENUE/TOTALREVENUE as SHARE

Resident TempTable;

DROP Table TempTable;

• ###### Re: Sum function in Qlik Sence data editor does not work

Thank you very much, Sunny, for being such a big help!

• ###### Re: Sum function in Qlik Sence data editor does not work

No problem at all