# 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
Did you mean:
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 ?

 Customer Item Sales Qty Sales Amount AVG Sales Price Purchases Qty Purchases Amount with AVG Sale Price 145,00 201,87 172,00 239,46 022447945 111164 9,00 7,54 0,84 0,00 0,00 022447945 111357 3,00 7,44 2,48 0,00 0,00 022447945 111854 18,00 9,90 0,55 0,00 0,00 022447945 113756 4,00 8,00 2,00 12,00 24,00 022447945 116373 15,00 6,00 0,40 48,00 19,20 022447945 119626 4,00 1,64 0,41 0,00 0,00 022447945 120485 10,00 16,95 1,70 19,00 32,21 022447945 121408 10,00 22,20 2,22 16,00 35,52 022447945 121496 10,00 11,15 1,12 25,00 27,88 022447945 122850 2,00 8,90 4,45 0,00 0,00 022447945 123709 3,00 13,38 4,46 0,00 0,00 022447945 129563 3,00 3,09 1,03 0,00 0,00 022447945 134488 8,00 16,00 2,00 10,00 20,00 022447945 140278 5,00 10,20 2,04 6,00 12,24 022447945 142798 1,00 3,24 3,24 0,00 0,00 022447945 145606 12,00 23,52 1,96 24,00 47,04 022447945 148173 2,00 3,84 1,92 0,00 0,00 022447945 148324 7,00 9,82 1,40 0,00 0,00 022447945 148441 1,00 0,58 0,58 0,00 0,00 022447945 148501 1,00 2,35 2,35 0,00 0,00 022447945 148745 1,00 0,58 0,58 0,00 0,00 022447945 148831 12,00 11,85 0,99 12,00 11,85 022447945 148842 2,00 2,00 1,00 0,00 0,00 022447945 148878 2,00 1,70 0,85 0,00 0,00
1 Solution

Accepted Solutions
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.

3 Replies
MVP

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
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.

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.

Community Browser