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
