Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator

Problem with total summate and partial summate in a straight chart

Hello,

I have problem with total summate and partial summate in a straight chart.

I upload sales and purchases in some items i don't have sales and in other i don't have purchases for the same period.

The problem is in the column Purchases Amount with AVG Sale Price. 

The expression is Purchases Qty*(Sales Amount/Sales Qty)

if you summate the partial rows you have 229,93 and in the total row the chart shows 239,46 with total mode (Expression Total) , if you change the total mode to (Sum of Rows) you have the correct summate 229,93.

The problem begins when i summate only in the customer. In this case the amount does not change i have  239,46

I tried to change the expression to 

SUM([Purchases Qty])
*
(sum({<Item= {"=sum([Purchases Qty]) <> 0"}>} [Sales Amount])
/
sum({<Item= {"=sum([Purchases Qty]) <> 0"}>} [Sales Qty])) 

but the total amount is 251,7 that is not correct.

Can anyone help in this case ?

Thank you in advance.

CustomerItemSales QtySales AmountAVG Sales PricePurchases QtyPurchases Amount with AVG Sale Price
  145,00201,87 172,00239,46
0224479451111649,007,540,840,000,00
0224479451113573,007,442,480,000,00
02244794511185418,009,900,550,000,00
0224479451137564,008,002,0012,0024,00
02244794511637315,006,000,4048,0019,20
0224479451196264,001,640,410,000,00
02244794512048510,0016,951,7019,0032,21
02244794512140810,0022,202,2216,0035,52
02244794512149610,0011,151,1225,0027,88
0224479451228502,008,904,450,000,00
0224479451237093,0013,384,460,000,00
0224479451295633,003,091,030,000,00
0224479451344888,0016,002,0010,0020,00
0224479451402785,0010,202,046,0012,24
0224479451427981,003,243,240,000,00
02244794514560612,0023,521,9624,0047,04
0224479451481732,003,841,920,000,00
0224479451483247,009,821,400,000,00
0224479451484411,000,580,580,000,00
0224479451485011,002,352,350,000,00
0224479451487451,000,580,580,000,00
02244794514883112,0011,850,9912,0011,85
0224479451488422,002,001,000,000,00
0224479451488782,001,700,850,000,00
1 Solution

Accepted Solutions
geogou1973
Creator
Creator
Author

I changed the expression to 

Sum(Aggr(Sum(PURCH_QTY) *
(Sum(SALES_AMT) / Sum(SALES_QTY)), CUST01, ITEM_ID))

and worked.

Thank you very much.

 

View solution in original post

3 Replies
MayilVahanan

Hi @geogou1973 

Try like below

Purchases Amount with AVG Sale Price:
If(IsNull(RowNo()) or RowNo() = 0, Sum(Aggr(Sum([Purchases Qty]) * (Sum([Sales Amount]) / Sum([Sales Qty])), Customer, Item)),
Sum([Purchases Qty]) * (Sum([Sales Amount]) / Sum([Sales Qty])))

Thanks & Regards,
Mayil Vahanan R
geogou1973
Creator
Creator
Author

Hello and thank you for the response.

In the chart a have only the dimension of the customer the expression works correctly in the total summate with total mode (Expression Total)  but in the partial does not work. I have different amount.

 

 

 

geogou1973
Creator
Creator
Author

I changed the expression to 

Sum(Aggr(Sum(PURCH_QTY) *
(Sum(SALES_AMT) / Sum(SALES_QTY)), CUST01, ITEM_ID))

and worked.

Thank you very much.

 

View solution in original post