Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

victor01
New Contributor II

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

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?

MVP
MVP

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

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;

victor01
New Contributor II

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.

MVP
MVP

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

Can you share a screenshot of the error message?

mikaelsc
Contributor III

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

victor01
New Contributor II

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!

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.

MVP
MVP

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

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;

victor01
New Contributor II

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

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

MVP
MVP

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

No problem at all

Community Browser