QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for
Search instead for
Did you mean:
Not applicable

How do i find the Total Value of Sale/Qty

Hi,

I am not getting Below value(Red Color) by using this formula of Purchase Ton = Basic Value / Qty. If i use this formula i am getting my expression total(Blue color).

Cany any one pls help me to find the below value which is in Red  Color.

 Date GR_Num Purchase_Item Qty Rate Value Basic Value Pur Rate Ton 74.98 1794445 1,858,004.89 72047.69 05-09-2011 3436 0101311 21.33 23,300.00 496989 532,194.24 24,950.50 09-09-2011 3553 0101311 36.47 24,600.00 897162 897,162.00 24,600.00 10-09-2011 3561 0101311 17.18 23,300.00 400294 428,648.65 24,950.45 Out Put Will Be Date GR_Num Purchase_Item Qty Rate Value Basic Value Pur Rate Ton 74.98 1794445 1,858,004.89 24,780.01 05-09-2011 3436 0101311 21.33 23,300.00 496989 532,194.24 24,950.50 09-09-2011 3553 0101311 36.47 24,600.00 897162 897,162.00 24,600.00 10-09-2011 3561 0101311 17.18 23,300.00 400294 428,648.65 24,950.45 Pur Rate Ton = Basic Value / Qty

Regards,

Nirmal.

1 Solution

Accepted Solutions
MVP

Just follow the same pattern:

=sum( aggr( YOURCHARTEXPRESSION, YOURCHARTDIMENSIONS))

9 Replies
Master

The red looks like the average. Try changing the expression total to Sum of Rows instead.

EDIT: or Average of Rows if you're trying to get the red result. Can't tell for sure

Not applicable

Hi Johannes,

Thanks for your reply.

i tried but im not getting the above answer. i dont need the expression total. i want the Total Qty/ Total Basic Sale Value.

Thanks,

Nirmal.

MVP

I think average of rows will not work here, since I believe Nirmal would need a weighted average here.

Nirmal, what are your expressions used for Basic Value and Qty? Could you post a small sample file?

Not applicable

Can any help to me sort out this problem.....

Not applicable

Hi Swuehl,

Qty =sum(if(gra_id=5,grd_rcv_qty - grd_rej_qty))

Basic Value =

Num(sum(if(gra_id=5,grd_rcv_qty - grd_rej_qty))*sum(if(gra_id=10,Gr_Rate)))

+ sum( if(Grd_Group='Insurance',Gr_Rate)) + sum( if(Grd_Group='Service Charge',Gr_Rate))

+

sum( if(Grd_Group='Freight',Gr_Rate)) +  sum( if(Grd_Group='Loading',Gr_Rate)) + sum( if(Grd_Group='Forwarding',Gr_Rate))  +

sum( if(Grd_Group='Other Charges',Gr_Rate)) - sum( if(Grd_Group='Other Discount',Gr_Rate)) - sum( if(Grd_Group='Discount',Gr_Rate))

MVP

And you are not using sum-of-rows as total mode for Qty and/or Basic Value?

If you do, you probably need to use the technique sum-of-rows using advanced aggregation (there is a chapter in the Help about this), to calculate the sum of rows for numerator and denominator, for your Qty, it will look like

=sum(aggr( sum(if(gra_id=5,grd_rcv_qty - grd_rej_qty)), YOURCHARTDIMENSION))

Not applicable

How do i use aggr for Basic value formula ?

MVP

Just follow the same pattern:

=sum( aggr( YOURCHARTEXPRESSION, YOURCHARTDIMENSIONS))

Not applicable

Hi Swuehl,

Thanks a lot.....

Regards,

Nirmal.

Tags