Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

thewillemzelluf
New Contributor II

different granularity

HI all,

i have a problem. I need to sum the total reveneu of an invoice but it consists of 2 components:


1. goods amount

       

invoice numberreveneuproduct_ID
15478 €    40,00 TAT
15478 €    20,55 PTH
15478 €    79,10 JFD
15478 €    88,50 FFT

2. transport

 

invoice numbertransport costs
15478

120

If i do an applymap with the invoice nummers als map_ID it will add transport to all the rows containing the same invoice number. How can i add the transport turnover once per invoice?

thank you in advance!

1 Solution

Accepted Solutions
vvira1316
Valued Contributor II

Re: different granularity

I don't think you need to use applymap. Not sure what your data model looks like.

try following

Sum({<invoice number>}revenue) + Sum({<invoice number>}transport costs)

3 Replies
vvira1316
Valued Contributor II

Re: different granularity

I don't think you need to use applymap. Not sure what your data model looks like.

try following

Sum({<invoice number>}revenue) + Sum({<invoice number>}transport costs)

dwforest
Valued Contributor

Re: different granularity

Note for Vijay's solution you would need to keep the good and transport as separate files, Qlik will avoid the fan trap (multiplying the transport number by the number of goods lines).

Another solution is to categorize the amounts during the load and append transport costs into a single table

Load

Invoice_number, revenue as amount, prooduct_id, 'goods' as amount_type

from goods_table;

Load

invoice_number, tranport_costs as amount, '' as product_id, 'tranport' as amount_type

another approach would be to create a 'product_id' for transport costs

thewillemzelluf
New Contributor II

Re: different granularity

Thats the solution, Thankyou!!

Community Browser