Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Search instead for
Did you mean:
Creator

## Sum of rows value differs from Auto and Sum function

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

Labels (1)
• ### Auto Sum Total functions

1 Solution

Accepted Solutions
MVP

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))``````
2 Replies
MVP

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))``````
Creator
Author

Thanks a lot, it works.