Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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…
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!
This is because aggregate functions usually need Group By Clause. Which dimensions are you want to do the sum over? Sum over Order_ID?
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;
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.
Can you share a screenshot of the error message?
remove
(UNIT_PRICE*QUANTITY)/Sum(UNIT_PRICE*QUANTITY) AS SHARE
from your first tempTable
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.
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;
Thank you very much, Sunny, for being such a big help!
No problem at all