sum of row count value differs

[venkata]

Hi Friends,

I am getting the below row sum issue when i use aggr  expression in set analysis. Also, i need to consider the values till 2020 and unable to skip 2021. Below is my expression

My Expression :sum(distinct aggr(NODISTINCT SUM(distinct{<MAIN_PROJECT1-={'-'},[YEAR]={"<=2020"}>}[PROPOSED BUDGET]),MAIN_PROJECT1))

issue: In the qlikview list box, the sum of value shows 440000 but after exporting the values in to excel then the sum count is showing as  2640000 and 2640000 is correct. How can i make the list box count as 2640000.

outcome:

Excel total sum:

[Kushal_Chawda]

@venkata  Could be due to distinct try removing distinct and check

or do you really need aggr what you get without aggr ?

[venkata]

Hi Kush,

If i remove distinct then the out come of the sum is wrong(getting a big number). If i don't use aggr then i can skip year 2021 but the total sum in the table is 480000 and in the excel it is 2000000( i want to get 2640000).

SUM(distinct{<MAIN_PROJECT1-={'-'},[YEAR]={"<=2020"}>}[PROPOSED BUDGET])

and excel count is:2000000

[Kushal_Chawda]

@venkata  try below instead, Need to include straight table dimensions as well.

sum(distinct aggr(NODISTINCT SUM(distinct{<MAIN_PROJECT1-={'-'},[YEAR]={"<=2020"}>}[PROPOSED BUDGET]),YEAR,type))

or

sum(distinct aggr(NODISTINCT SUM(distinct{<MAIN_PROJECT1-={'-'},[YEAR]={"<=2020"}>}[PROPOSED BUDGET]),YEAR,type,MAIN_PROJECT1))

[venkata]

Hi Kush,

Thanks for the reply. the out put is as follows:

The total after exporting into excel is 2080000 the expected value is 2640000

[Kushal_Chawda]

@venkata  Would you be able to share sample app to look at? 

[venkata]

Hi Kush,

after changing the expression sum( aggr( SUM(distinct{<MAIN_PROJECT1-={'-'},[YEAR]={"<=2020"}>}[PROPOSED BUDGET]),YEAR,type,MAIN_PROJECT1)) to this it is working fine.Thank you for your help.