Sum of rows value differs from Auto and Sum function

[vsap2000]

Hello Guys,

I am having this Prod Volume calculation table, when Current Year Quantity = Last Year Quantity then my Prod Vol will be 0, if it is not same value than use Aggr expression shown below,when I use this equation I am getting total value using Qlik Sense table total function as Auto as shown below in A, when I export this in excel or use Qlik Sense table Sum function then value differs and shows value in B, reason it seems using If Current Year Quantity = Last Year Quantity, how I can make Prod Val shown in B (red text)

A) Using Auto function of table:

Prod  Curr Yr Sales Qty Last Yr Sales Qty Prod Vol

----------------------------------------------------------------

Total                31               41                               50

---------------------------------------------------------------

A                        10                20                              20  

B                         5                5                                 0 

C                        6                 16                              20

B) Using  Sum function of table

Prod  Curr Yr Sales Qty Last Yr Sales Qty Prod Vol

-----------------------------------------------------------------

Total                31               41                               40

-----------------------------------------------------------------

A                        10                20                             20  

B                         5                5                                  0

C                        6                 16                              20

I should get Prod Vol as shown above in B

Prod Vol calculation I am using:

If(Sum({<CurrentYear = {1}>}[SalesQty]) = Sum({<LastYear = {1}>}[SalesQty]),
0,

Sum(Aggr(If((Sum({<CurrentYear = {1}>}[Sales Inv]) = 0 or
                           Sum({<LastYear = {1}>}[Sales Inv]) = 0),
                          (Sum({<CurrentYear = {1}>}[GM Inv]) -
                           Sum({<LastYear = {1}>}[GM Inv])),
                              ((Sum({<CurrentYear = {1}>} [SalesQty]) -
                               ((Sum({<LastYear = {1}>} [SalesQty])/
                                 Sum({<LastYear = {1},
                                              [ Prod Exclde Ind] = {0} >} TOTAL [SalesQty]))   *
                                  Sum({<CurrentYear = {1},
                                               [Prod Exclude Ind] = {0} >} TOTAL [SalesQty])))

                             *
                     Alt(Sum({<LastYear = {1}>}[GM Inv] )/Sum({<LastYear = {1}>}[SalesQty]),0))),

           Customer_Id, Prod_Id))
)

Please let me know how I can achieve Prod Vol shown in B.

Thanks in advance

-V

[sunny_talwar · Accepted solution]

May be this

Sum(Aggr(
If(Sum({<CurrentYear = {1}>}[SalesQty]) = Sum({<LastYear = {1}>}[SalesQty]),
0,

Sum(Aggr(If((Sum({<CurrentYear = {1}>}[Sales Inv]) = 0 or
                           Sum({<LastYear = {1}>}[Sales Inv]) = 0),
                          (Sum({<CurrentYear = {1}>}[GM Inv]) -
                           Sum({<LastYear = {1}>}[GM Inv])),
                              ((Sum({<CurrentYear = {1}>} [SalesQty]) -
                               ((Sum({<LastYear = {1}>} [SalesQty])/
                                 Sum({<LastYear = {1},
                                              [ Prod Exclde Ind] = {0} >} TOTAL [SalesQty]))   *
                                  Sum({<CurrentYear = {1},
                                               [Prod Exclude Ind] = {0} >} TOTAL [SalesQty])))

                             *
                     Alt(Sum({<LastYear = {1}>}[GM Inv] )/Sum({<LastYear = {1}>}[SalesQty]),0))),

           Customer_Id, Prod_Id))
)
, Prod_Id))

[vsap2000]

Thanks a lot, it works.