Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
raz-datamind
Partner - Contributor
Partner - Contributor

aggr() function to sum up transaction data

Hello,

I have a data of transactions:

category, supplier, brand, item, etc'.

each transaction may contain more then one row, category has several suppliers, which has several brand, which has several items.

also for each transaction row i have a field indicating the number of clients (1,0 or -1)

I want to create two tables with category, supplier & brand as dimensions,

and sum(amount) over quantities as expressions, in one table. and sum(clients) over quantities in the second

meaning my expressions are sum(amount) for all transaction that has 1 item, sum(amount) for all transaction that has 2 items, etc'.

and sum(clients) for all transaction that has 1 item, sum(clients) for all transaction that has 2 items, etc'.

i'm trying to use the expressions:

sum((if(aggr(sum(Sales_Qnty), transaction, category, supplier, brand)=1,amount)

sum((if(aggr(sum(Sales_Qnty), transaction, category, supplier, brand)=1,clients)

but it's not adding up:

i get 1 client for:

make up, accessories, revlon

make up, accessories, D&G

but when i collapse brand i get

make up, accessories, 2 client.

when the correct answer is 1 client, as 1 client made the all transaction buying all those items.

on the amount table on the other hand i have a brand with two items, each costs 1.5$.

i get a line:

sweets & food, sweets, must, 1.5$

instead of :

sweets & food, sweets, must, 3$

and of course both table don't add up when i choose more than one transaction.

Anyone can direct me in the right way?

Do i use expreesions, or can i create calculated dimension to get the 1,2,3,... quantities as dimension?

Thanks,

Raz

0 Replies