Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator
‎2020-11-13 02:30 AM

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
Preview file
11 KB
453 Views
0 Likes
1 Solution

Accepted Solutions
geogou1973
Creator
Creator
‎2020-11-13 03:52 AM
Author

In response to MayilVahanan

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

428 Views
0 Likes
3 Replies
MayilVahanan
MVP
MVP
‎2020-11-13 03:03 AM

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
Please close the thread by marking correct answer & give likes if you like the post.
446 Views
0 Likes
geogou1973
Creator
Creator
‎2020-11-13 03:39 AM
Author

In response to MayilVahanan

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.

 

 

 

434 Views
0 Likes
geogou1973
Creator
Creator
‎2020-11-13 03:52 AM
Author

In response to MayilVahanan

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.

 

429 Views
0 Likes