Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marishnagendran
Contributor

Table Box or in Pivot Table

Hi All,

Product NameCustomer NameQtyNet Profit
Total 26598
Product ACustomer A102
 Customer B52
 Customer C155
 Customer D207
Product BCustomer E102
 Customer A245
 Customer B164
 Customer F5012
 Customer Z205
Product CCustomer Q4020
 Customer A202
 Customer B3532

 

This is my above transaction table.

I want to calculate Every product Average Net Profit based on above transaction table (i.e)

Item GroupAvg_Net Profit
Product A4
Product B5.6
Product C18

 

Once calculated i want to compare with each product average with transaction table.

If value is less than the average i want to take that transaction (i.e)

My Output

Product NameCustomer NameQtyNet Profit
Product ACustomer A102
 Customer B52
Product BCustomer E102
 Customer A245
 Customer B164
 Customer Z205
Product CCustomer A202
9 Replies
marishnagendran
Contributor

Re: Table Box or in Pivot Table

Is it possible or not

Edvin
New Contributor III

Re: Table Box or in Pivot Table

You could try something like this:

=IF(aggr(avg([Net Profit]),[Product name])<[Net Profit],[Product Name])

marishnagendran
Contributor

Re: Table Box or in Pivot Table

Thanks for your reply..

It's not working edvin

lanlizgu
Contributor III

Re: Table Box or in Pivot Table

Straight Table with Product Name and Customer Name as dimensions and the following expressions:

if([Net Profit]<
(sum(total <[Product Name]>[Net Profit])
/
count(total <[Product Name]> [Customer Name])
)
,[Net Profit]
,null()
)

 

 

 

if([Net Profit]<
(sum(total <[Product Name]>[Net Profit])
/
count(total <[Product Name]> [Customer Name])
)
,Qty
,null()
)

 

 

marishnagendran
Contributor

Re: Table Box or in Pivot Table

It's not working

lanlizgu
Contributor III

Re: Table Box or in Pivot Table

It works perfectly. PFA a .qvf attached

marishnagendran
Contributor

Re: Table Box or in Pivot Table

I think my net profit column is calculated field that's why it shows syntax error

lanlizgu
Contributor III

Re: Table Box or in Pivot Table

could you please share your data so I could check?

marishnagendran
Contributor

Re: Table Box or in Pivot Table

Net Profit Column is based on Expression

((Sum(kpi_MR)/100000 )

/

(Sum(Kpi_SaleValue)/100000) * 100