Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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_IDUNIT_PRICEQUANTITY
1024834,85
1024918,69
102507,710
1025016,815
1025116,820
1025264,840
102531020
102543,615
1025515,220
1025626,215
1025735,125
1025825,66
10259810
102601221
1026114,420
1026230,42
10263836
102647,725
1026531,230
1026630,412
1026714,750
1026827,84
10269260

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:

result1.png

(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…

LOAD

    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!

9 Replies
sunny_talwar

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

sunny_talwar

May be try this

TempTable:

LOAD 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);

Left Join (TempTable)

LOAD ORDER_ID,

    Sum(REVENUE) as TOTALREVENUE

Resident TempTable

Group By ORDER_ID;

FinalTable:

LOAD ORDER_ID,

    UNIT_PRICE,

    QUANTITY,

    REVENUE,

    REVENUE/TOTALREVENUE as SHARE

Resident TempTable;

DROP Table TempTable;

Anonymous
Not applicable
Author

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.

sunny_talwar

Can you share a screenshot of the error message?

mikaelsc
Specialist
Specialist

remove

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


from your first tempTable

Anonymous
Not applicable
Author

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!

shot#1.png

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

shot#2.png


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.

sunny_talwar

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

TempTable:

LOAD ORDER_ID,

    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:

LOAD ORDER_ID,

    UNIT_PRICE,

    QUANTITY,

    REVENUE,

    REVENUE/TOTALREVENUE as SHARE

Resident TempTable;

DROP Table TempTable;

Anonymous
Not applicable
Author

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

sunny_talwar

No problem at all